Hi,
I am attempting to create a script that draws from an excel sheet of 'clients', with respecitive primary and secondary contacts for each (including emails). Unfortuantely, the email being sent out is relatively standard, but one unique attachment needs to be made to each client, this will be done manually.
As of now, I am able to draw email address from Excel columns and create new emails to send out, but I am having trouble copying in a large amount of text for the body of the message. So far I have:
Which basically creates a standard email using the emails in Column B and respecitive names in A. Right now it only displays a short one sentence message, but I am looking to add in about a Word documents worth of text into each email.
Above is about the amount of text that will need to be imported into Body of the email. Are there any easier ways to import this rather than hard coding the message into the VBA script? Possibly using a copy/paste approach? Also another question, is it possible to change the .From field so it appears that the message is being sent from a distribution list as opposed to a single person who will be running the script?
Thanks very much for the help.
I am attempting to create a script that draws from an excel sheet of 'clients', with respecitive primary and secondary contacts for each (including emails). Unfortuantely, the email being sent out is relatively standard, but one unique attachment needs to be made to each client, this will be done manually.
As of now, I am able to draw email address from Excel columns and create new emails to send out, but I am having trouble copying in a large amount of text for the body of the message. So far I have:
Code:
Sub Test1()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "C").Value) = "yes" Then
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Reminder"
.Body = "Dear " & Cells(cell.Row, "A").Value _
& vbNewLine & vbNewLine & _
"Please contact us to discuss bringing " & _
"your account up to date"
.Display 'Or use Send
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
Which basically creates a standard email using the emails in Column B and respecitive names in A. Right now it only displays a short one sentence message, but I am looking to add in about a Word documents worth of text into each email.
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nullam eget nisi tincidunt, pellentesque nulla imperdiet, pulvinar lectus. Donec id pharetra velit. Vivamus pretium molestie arcu eu sodales. Maecenas aliquam dui purus, nec lobortis justo vehicula quis. Morbi nisi lacus, rhoncus sit amet ipsum sed, sodales euismod nulla. Fusce dui nisl, porttitor vitae mi vel, ullamcorper sagittis neque. Phasellus non placerat ipsum, nec elementum enim. Nam tempus imperdiet purus. Phasellus cursus libero sodales ligula pellentesque hendrerit. Suspendisse ut sagittis tortor. Donec vehicula tortor sed purus tincidunt ornare. In hac habitasse platea dictumst. Vestibulum sed porta arcu. Sed fringilla magna sed accumsan interdum.
Aenean et tortor sed diam suscipit imperdiet. Maecenas ac lobortis justo, in vulputate metus. Quisque at mi ut nulla sagittis semper. Cras adipiscing enim nunc, a lacinia lectus eleifend sagittis. Proin molestie mauris in libero volutpat tincidunt. Morbi nec elit ipsum. Sed odio nibh, tincidunt bibendum dapibus at, pulvinar pulvinar lorem. Fusce condimentum lacus leo, et varius eros rhoncus a. Vestibulum magna augue, dictum vitae faucibus auctor, blandit in enim. Etiam fringilla odio non turpis varius, non hendrerit risus blandit. Curabitur orci erat, rhoncus eu arcu sed, consequat porttitor est. Proin vulputate at libero consectetur luctus. Aliquam volutpat quis lorem sit amet hendrerit.
Sed luctus ligula neque, sed luctus tellus dictum ut. Pellentesque quis orci eget tellus suscipit scelerisque. Integer vitae nulla gravida ipsum hendrerit dapibus. In eu justo eu nisl placerat rhoncus. Duis viverra pulvinar nisl eu ultrices. Curabitur mattis commodo tortor, a tincidunt nibh adipiscing nec. Sed elementum, sapien a sollicitudin ullamcorper, metus quam venenatis turpis, nec dictum elit lorem a urna.
Nulla mattis a purus aliquet viverra. Suspendisse id posuere tellus. Ut at ornare orci. Etiam tincidunt orci enim, ac auctor augue dignissim at. Quisque auctor semper nunc, sit amet semper leo venenatis quis. Donec quis ornare metus. Vestibulum cursus libero sed tincidunt dictum. Duis tristique, justo sed pulvinar congue, eros metus pharetra orci, non rhoncus lorem dui vel ipsum. Praesent ante lorem, mollis id blandit nec, commodo at nibh. Etiam vel consequat turpis. Nulla facilisi. Aliquam sit amet convallis felis. Aenean tempus tortor ornare metus sollicitudin, at facilisis quam condimentum. Nullam id urna dolor. Pellentesque adipiscing nunc auctor sapien commodo, quis commodo orci scelerisque.
Integer ultricies arcu tellus, quis ultrices nulla pretium at. Sed scelerisque interdum laoreet. Ut vel purus blandit, placerat risus at, laoreet nisl. Vivamus ut odio euismod, vestibulum purus eu, vehicula diam. Phasellus pellentesque urna ut quam pellentesque luctus. Nullam sagittis semper neque in luctus. Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas. Sed commodo rutrum nisl, non congue augue bibendum sit amet. Nam vitae imperdiet nulla. Aliquam venenatis odio neque, a posuere sapien cursus a. Nullam faucibus nulla tellus, in bibendum velit consectetur in. Maecenas facilisis consequat lectus, eu suscipit tellus volutpat eu.
Above is about the amount of text that will need to be imported into Body of the email. Are there any easier ways to import this rather than hard coding the message into the VBA script? Possibly using a copy/paste approach? Also another question, is it possible to change the .From field so it appears that the message is being sent from a distribution list as opposed to a single person who will be running the script?
Thanks very much for the help.