GSD4ME
asked on
VB and mailing lists in Excel
I have a database of club members in an excel spreadsheet.
Data includes names, address and e-mail address
Every 6 months I want to e-mail EACH member with THEIR own details (name, address, membership details etc.)
I have written VB macros to extract the relevant info for each member from their set of data and format into what would bethe contents of a message to be sent.
Q: How do I SEND this block of data to that person's e-mail address using VB in EXCEL.
I don't want to 'drag' the data into Outlook, I want to 'push' it from Excel.
How do I do it? CAN I do it?
I do not want to have to change to a new database (access for example) as this has taken a long time to set up and not everyone in the organisation has Access but we do all have excel so the person who takes this job over from me will just be able to run the appropriate macro.
Any suggestions?
Data includes names, address and e-mail address
Every 6 months I want to e-mail EACH member with THEIR own details (name, address, membership details etc.)
I have written VB macros to extract the relevant info for each member from their set of data and format into what would bethe contents of a message to be sent.
Q: How do I SEND this block of data to that person's e-mail address using VB in EXCEL.
I don't want to 'drag' the data into Outlook, I want to 'push' it from Excel.
How do I do it? CAN I do it?
I do not want to have to change to a new database (access for example) as this has taken a long time to set up and not everyone in the organisation has Access but we do all have excel so the person who takes this job over from me will just be able to run the appropriate macro.
Any suggestions?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You may also want to add an Exit For when the last row of data is hit then it will not continue to go through all rows.
You could use this excellent free tool:
http://www.freevbcode.com/ShowCode.Asp?ID=109
http://www.freevbcode.com/ShowCode.Asp?ID=109
ASKER
Many thanks - you were the closest to what I needed
Sub Email()
Dim objOL As Object
Dim objEMail As Object
Set objOL = CreateObject("Outlook.Appl
For x = 1 To Sheet1.Rows.Count
If Sheet1.Cells(x, 1) <> "" Then
Set objEMail = objOL.CreateItem(olMailIte
With objEMail
.Recipients.Add Sheet1.Cells(x, 1) 'Cell of the email address
.Subject = "Your details "
.Body = "Details"
'To send an attachment
'.Attachments.Add ("C:\my documents\file.txt")
.Send
End With
Set objEMail = Nothing
End If
Next
End Sub