Lotus notes macro problem 1

[Jimmylaki]

Hello,

I have the following code which attaches an file to an email and sends to a distribution list. The problem I have is that I want the email to pick up the same name as the file attachment. It can change the code to factor in a different attachment name due to date stamp.

I wish to also check the atatchment before it is sent to check that the file is correct. what is the best way to put a limit on the code that I can check the file and then send?

Testing this code I sent an email to myself but opening the attachment there is an excel message asking if I wish to update links but when I open the file saved in its drive there is no such message. How do I stop this message. Any pointers for these areas would be greatly appreciated.



Sub lotuscode()
Dim oSess As Object
Dim oDB As Object
Dim oDoc As Object
Dim oItem As Object
Dim direct As Object
Dim Var As Variant
Dim flag As Boolean

Set oSess = CreateObject("Notes.NotesSession")
Set oDB = oSess.GETDATABASE("", "")
Call oDB.OPENMAIL
flag = True
If Not (oDB.IsOpen) Then flag = oDB.Open("", "")

If Not flag Then
MsgBox "Can't open mail file: " & oDB.SERVER & " " & oDB.FILEPATH
GoTo exit_SendAttachment
End If
On Error GoTo err_handler

'Building Message
Set oDoc = oDB.CREATEDOCUMENT
Set oItem = oDoc.CREATERICHTEXTITEM("BODY")
oDoc.Form = "Memo"
oDoc.Subject = "Subject in here"
oDoc.sendto = "Email address or Group here"
oDoc.body = ""
oDoc.postdate = Date
oDoc.SaveMessageOnSend = True

'Attaching DATABASE
Call oItem.EmbedObject(1454, "", "ILocation of the file you wish to send here inc the .xls etc")
oDoc.visable = True
'Sending Message
oDoc.SEND False
exit_SendAttachment:
On Error Resume Next
Set oSess = Nothing
Set oDB = Nothing
Set oDoc = Nothing
Set oItem = Nothing
'Done
Exit Sub
err_handler:
If Err.Number = 7225 Then
MsgBox "File doesn't exist"
Else
MsgBox Err.Number & " " & Err.Description
End If
On Error GoTo exit_SendAttachment
End Sub

 

[HughLerwill]

You appear to have three questions;
1. How to name/ rename an .xls file to reflect its date.
2. How to make the email visible in Lotus so it and its attachments can be checked before the email is (manually) sent.
3. How to prevent the links message.

Please confirm.

 

[Jimmylaki]

Hello,

to clarify question 1.
I want the subject line in lotus notes to be the same as the file name. ie if my file is report_05OCT09 I want the subject line of the email to be the same but in the code example I have posted I am unable to tweak the code to be the same as the attachment.

questions 2 and 3 confirmed as correct.
thank you
JL

 

[HughLerwill]

1. Does'nt oDoc.Subject = strFileName work?

1 & 2. wiglaer's code in thread222-766157 should be interesting for you. It's in vb6 but should translate easily.

3. Does the Workbook you are attaching have references to other Workbooks or are you getting the update links message for apparently no reason. Try attaching a really simple Workbook which you know has no outside references and see if that works.

 

[Jimmylaki]

Hello,

Thank you very much for your help
I will check test the code at work tomorrow.

re question 3. I have opened the file in its saved location ie outside the notes session and there is no link it seems something is happening when it is attached to the notes session causing the file to be `linked`.
regards
JL

 

[HughLerwill]

3. Does the same thing happen with the 'really simple' Workbook as suggested?

 

[Jimmylaki]

Hi,

On the forum link you sent I found the following code for Lotus emailing. Using this code there is no excel link message when I open the attachment. On the original code I sent a blank workbook but still got the message so was not sure which part of the code was causing the file to be linked. This code can also be set to manually send the email. I just need to work out how to add an index of email addresses ie more than 10. I guess this would need to be some kind of function.

Sub Lotusemail()

Dim objNotesSession As Object
Dim objNotesMailFile As Object
Dim objNotesDocument As Object
Dim objNotesField As Object


'On Error GoTo SendMailError

EMailSendTo = "email address here" '' Required - Send to address
EMailCCTo = "" '' Optional
EMailBCCTo = "" '' Optional
EmailSubject = "Test"

''Establish Connection to Notes
Set objNotesSession = CreateObject("Notes.NotesSession")

''Establish Connection to Mail File
'' .GETDATABASE("SERVER", "FILE")
Set objNotesMailFile = objNotesSession.GetDatabase("", "")
''Open Mail
objNotesMailFile.OPENMAIL

''Create New Memo
Set objNotesDocument = objNotesMailFile.CreateDocument

''Create 'Subject Field'
Set objNotesField = objNotesDocument.APPENDITEMVALUE("Subject", EmailSubject)

''Create 'Send To' Field
Set objNotesField = objNotesDocument.APPENDITEMVALUE("SendTo", EMailSendTo)

''Create 'Copy To' Field
'Set objNotesField = objNotesDocument.APPENDITEMVALUE("CopyTo", EMailCCTo)

''Create 'Blind Copy To' Field
'Set objNotesField = objNotesDocument.APPENDITEMVALUE("BlindCopyTo", EMailBCCTo)

''Create 'Body' of memo
Set objNotesField = objNotesDocument.CreateRichTextItem("Body")

With objNotesField
.AppendText "Hello,"
.addnewline 1
.addnewline 2
.AppendText "Please see the attached file for today's report:"
.addnewline 3
.AppendText "This e-mail is generated automatically. If you have any questions please contact....."
.addnewline 4
.AppendText ""
.addnewline 5
End With

''Attach the file --1454 indicate a file attachment
objNotesField = objNotesField.EmbedObject(1454, "", "U:\projects\daily Report.xls")

'objNotesField = objNotesField.EmbedObject(1454, "", ActiveWorkbook.FullName)

''Send the e-mail
objNotesDocument.Send (0)

''Release storage
Set objNotesSession = Nothing
Set bjNotesSession = Nothing
Set objNotesMailFile = Nothing
Set objNotesDocument = Nothing
Set objNotesField = Nothing

''Set return code
SendMail = True

On Error GoTo sendmailerror

sendmailerror:
Msg = "Is Lotus Notes Open?"


SendMail = False

End Sub

 

[HughLerwill]

I think;

.EMailSendTo = strMyArrayOfRecipients()

will do it. You will need to Dim and fill the array with your list of recipients email addresses.

 

[HughLerwill]

On re-reading your code perhaps;

Set objNotesField = objNotesDocument.APPENDITEMVALUE("SendTo", strMyArrayOfRecipients())