Hi All,
With a great deal of help from this forum I put together the following which allows me to send multiple emails to a list of addresses including a reference in the subject line and a set text in the message body. The problem I have is that I have moved from Lotus Notes to Outlook and, not being great at this, I’m unable to make the macro work. I’d massively appreciate any help any of you can give:
Sub TestingLotusNotesEmail()
Dim noSession As Object, noDatabase As Object, noDocument As Object
Dim obAttachment As Object, EmbedObject As Object
Dim stSubject As Variant, stAttachment As String
Dim vaRecipient As Variant, vaMsg As Variant
Dim Btext As String
Const EMBED_ATTACHMENT As Long = 1454
'Retrieve the path and filename of the active workbook.
stAttachment = ActiveWorkbook.FullName
'Initiate the Lotus Notes COM's Objects.
Set noSession = CreateObject("Notes.NotesSession")
Set noDatabase = noSession.GETDATABASE("", "")
'If Lotus Notes is not open then open the mail-part of it.
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
For i = 1 To 100
zSendTo = Sheet1.Cells(i, "B")
zRefs = Sheet1.Cells(i, "A") & " " & Sheet2.Range("B1")
xRefs = Sheet2.Range("A1")
'Create the e-mail and the attachment.
Set noDocument = noDatabase.CreateDocument
Set obAttachment = noDocument.CreateRichTextItem("stAttachment")
'Add values to the created e-mail main properties.
With noDocument
.Form = "Memo"
.SendTo = zSendTo
.Subject = "" & zRefs
.Body = xRefs
.SaveMessageOnSend = True
End With
'Send the e-mail.
With noDocument
.PostedDate = Now()
.Send 0, vaRecipient
End With
Next i
'Release objects from the memory.
Set EmbedObject = Nothing
Set obAttachment = Nothing
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing
'Activate Excel for the user.
AppActivate "Microsoft Excel"
MsgBox "The e-mail has successfully been created and distributed.", vbInformation
End Sub
Thanks in advance
Hailno
With a great deal of help from this forum I put together the following which allows me to send multiple emails to a list of addresses including a reference in the subject line and a set text in the message body. The problem I have is that I have moved from Lotus Notes to Outlook and, not being great at this, I’m unable to make the macro work. I’d massively appreciate any help any of you can give:
Sub TestingLotusNotesEmail()
Dim noSession As Object, noDatabase As Object, noDocument As Object
Dim obAttachment As Object, EmbedObject As Object
Dim stSubject As Variant, stAttachment As String
Dim vaRecipient As Variant, vaMsg As Variant
Dim Btext As String
Const EMBED_ATTACHMENT As Long = 1454
'Retrieve the path and filename of the active workbook.
stAttachment = ActiveWorkbook.FullName
'Initiate the Lotus Notes COM's Objects.
Set noSession = CreateObject("Notes.NotesSession")
Set noDatabase = noSession.GETDATABASE("", "")
'If Lotus Notes is not open then open the mail-part of it.
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
For i = 1 To 100
zSendTo = Sheet1.Cells(i, "B")
zRefs = Sheet1.Cells(i, "A") & " " & Sheet2.Range("B1")
xRefs = Sheet2.Range("A1")
'Create the e-mail and the attachment.
Set noDocument = noDatabase.CreateDocument
Set obAttachment = noDocument.CreateRichTextItem("stAttachment")
'Add values to the created e-mail main properties.
With noDocument
.Form = "Memo"
.SendTo = zSendTo
.Subject = "" & zRefs
.Body = xRefs
.SaveMessageOnSend = True
End With
'Send the e-mail.
With noDocument
.PostedDate = Now()
.Send 0, vaRecipient
End With
Next i
'Release objects from the memory.
Set EmbedObject = Nothing
Set obAttachment = Nothing
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing
'Activate Excel for the user.
AppActivate "Microsoft Excel"
MsgBox "The e-mail has successfully been created and distributed.", vbInformation
End Sub
Thanks in advance
Hailno