I have a spreadsheet with a list of e-mails addresses, together with a list of passwords relevant to the particular e-mail recipient.
Rather than manually creating an e-mail to each user, I am sure there must be a way of using VBA to run through the list, and send out an e-mail containing a standard piece of text together with the relevant password, and to loop through the list until one e-mail has been sent to each address.
(The password to be sent is in the column next to the relevant e-mail address)
Could anybody please enlighten me?
Many thanks
Paul Hibbert
Related resources
Replies (5)
Please login or register to join the discussion.
Just use Word
Hi Paul
You could do this more elegantly using the mail merge wizard in Word. This way you could set up a standard letter and insert the password in the appropriate place. Word facilitates using an Excel spreadsheet as the data source, with column headings in the first row.
Should be easy to do.
Carl
IT Mates
'Dedicated to Accountants'
www.itmates.co.za
Sample code
Paul
The following is sample code that could be used - shove it into a blank module in a workbook.
Assumptions:
1. You have headers in A1:C1 in a sheet:
A1: Email Address
A2: Subject of email (eg "Test")
A3: Email body text (eg "Your password is .....")
2. You have the relevant email addresses listed from A2 downwards - the code will take account of additional email addresses as you add them to the A column
3. In the B column from B2 downwards you have the relevant Subject text you want to apply.
4. In the C column from C2 downwards you have the relevant email body test you want to apply (eg could just be the password you want to send)
Then the code is:
Sub Send_Mail()
Dim OutApp As Object, OutMail As Object
Dim c As Range, myRng As Range, Ash As Worksheet
Set Ash = ActiveSheet
On Error GoTo end_code
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set myRng = Ash.Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each c In myRng
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = c.Value
.Subject = c.Offset(0, 1).Value
.HTMLBody = c.Offset(0, 2).Value
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Next c
end_code:
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
Note that this is only a partial solution since the security built into Outlook will prevent the code automatically sending the generated email - hence it is set to only display the generated email. You would then need to click Send on every email - which I appreciate could be a major pain the neck.
Anyway - hope you find it helpful!
Richard