Hello,
I have the following code that selects e-mail addresses from a table, then sends an attachment that I choose using a loop to each e-mail address.
I would like to split the body of the email into different parts in the following order: a salutation followed by a comma, a blank line after that, then a paragraph or two of text followed by a blank line then the attachment, then a closing.
Can anyone offer suggestions?
Option Compare Database
Sub Send_Mulitiple_Lotus_Notes_EMail()
Dim UserName As String
Dim MailDbName As String
Dim Maildb As Object
Dim MailDoc As Object
Dim AttachME As Object
Dim Session As Object
Dim EmbedObj1 As Object
Dim FileName As String
Dim Address As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst_Emp As DAO.Recordset
Set db = CurrentDb
Set rst_Emp = db.OpenRecordset("select * from [Employees]")
rst_Emp.MoveFirst
While Not rst_Emp.EOF
Address = rst_Emp!EmailAddress
Set rst = db.OpenRecordset("SELECT * FROM [tbl_FileName]")
rst.MoveFirst
' Open and locate current LOTUS NOTES User
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Session.GETDATABASE("", MailDbName)
If Maildb.IsOpen = True Then
Else
Maildb.OPENMAIL
End If
' Create New Mail and Address Title Handlers
Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = "Memo"
MailDoc.SendTo = Address
MailDoc.Subject = "Employee Communication"
MailDoc.Body = _
"Here is the latest update. If you no longer wish to receive these communications, please let us know. Additionally, if your e-mail address should change, please notify us immediately."
' use this to add a line break in the body of the e-mail
' Line1 = "This is line one of the multi-line text" & vbCrLf
' Line2 = "This is the second line"
' TotalString = Line1 & Line2
' Select File to Attach to E-Mail
MailDoc.savemessageonsend = True
FileName = rst!FileName
attachment1 = FileName 'Required File Name
If attachment1 <> "" Then
On Error Resume Next
Set AttachME = MailDoc.CREATERICHTEXTITEM("attachment1")
Set EmbedObj1 = AttachME.embedobject(1454, "attachment1", FileName, "")
On Error Resume Next
End If
MailDoc.PostedDate = Now()
On Error GoTo errorhandler1
MailDoc.SEND 0, Recipient
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing
rst_Emp.MoveNext
Wend
rst.Close
rst_Emp.Close
Set rst = Nothing
Set rst_Emp = Nothing
Exit Sub
errorhandler1:
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing
End Sub
I have the following code that selects e-mail addresses from a table, then sends an attachment that I choose using a loop to each e-mail address.
I would like to split the body of the email into different parts in the following order: a salutation followed by a comma, a blank line after that, then a paragraph or two of text followed by a blank line then the attachment, then a closing.
Can anyone offer suggestions?
Option Compare Database
Sub Send_Mulitiple_Lotus_Notes_EMail()
Dim UserName As String
Dim MailDbName As String
Dim Maildb As Object
Dim MailDoc As Object
Dim AttachME As Object
Dim Session As Object
Dim EmbedObj1 As Object
Dim FileName As String
Dim Address As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst_Emp As DAO.Recordset
Set db = CurrentDb
Set rst_Emp = db.OpenRecordset("select * from [Employees]")
rst_Emp.MoveFirst
While Not rst_Emp.EOF
Address = rst_Emp!EmailAddress
Set rst = db.OpenRecordset("SELECT * FROM [tbl_FileName]")
rst.MoveFirst
' Open and locate current LOTUS NOTES User
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Session.GETDATABASE("", MailDbName)
If Maildb.IsOpen = True Then
Else
Maildb.OPENMAIL
End If
' Create New Mail and Address Title Handlers
Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = "Memo"
MailDoc.SendTo = Address
MailDoc.Subject = "Employee Communication"
MailDoc.Body = _
"Here is the latest update. If you no longer wish to receive these communications, please let us know. Additionally, if your e-mail address should change, please notify us immediately."
' use this to add a line break in the body of the e-mail
' Line1 = "This is line one of the multi-line text" & vbCrLf
' Line2 = "This is the second line"
' TotalString = Line1 & Line2
' Select File to Attach to E-Mail
MailDoc.savemessageonsend = True
FileName = rst!FileName
attachment1 = FileName 'Required File Name
If attachment1 <> "" Then
On Error Resume Next
Set AttachME = MailDoc.CREATERICHTEXTITEM("attachment1")
Set EmbedObj1 = AttachME.embedobject(1454, "attachment1", FileName, "")
On Error Resume Next
End If
MailDoc.PostedDate = Now()
On Error GoTo errorhandler1
MailDoc.SEND 0, Recipient
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing
rst_Emp.MoveNext
Wend
rst.Close
rst_Emp.Close
Set rst = Nothing
Set rst_Emp = Nothing
Exit Sub
errorhandler1:
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing
End Sub