send email to each customer email in excel sheet.

[-keevill-]

I have got a customer list of names and addresses and email addresses in
excel 2003.
I want to send a new price list ( pdf ) and voucher ( jpg) to each email
address with a short covering email by way of explanation.
Can someone talk me thru that - it surely is very simple but I am a total
beginner in this .
Thanks,

 

[Ron de Bruin]

Try this

Copy the code below in a normal module of your workbook

In Sheet1 in a1:a100 the mail addresses

Change the path to the file names here
.Attachments.Add ("C:\test.jpg")
.Attachments.Add ("C:\test.pdf")



Sub Mail_Test_Outlook()
' Is working in Office 2000-2007
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim cell As Range
Dim strto As String

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

strbody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2" & vbNewLine & _
"This is line 3" & vbNewLine & _
"This is line 4"

For Each cell In ThisWorkbook.Sheets("Sheet1").Range("A1:A100")
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next cell
If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)

On Error Resume Next
With OutMail
.To = strto
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = strbody
.Attachments.Add ("C:\test.jpg")
.Attachments.Add ("C:\test.pdf")
.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[-keevill-]

I am afraid I need more help ... pls see below...my questions..

Try this

Copy the code below in a normal module of your workbook

what exactly is this 'normal module' ? where is it and how do I do it ?

In Sheet1 in a1:a100 the mail addresses

Is this part of the command ?

Change the path to the file names here
.Attachments.Add ("C:\test.jpg")
.Attachments.Add ("C:\test.pdf")

OK this is clear

 

[Ron de Bruin]

Hi

what exactly is this 'normal module' ? where is it and how do I do it ?
See
http://www.rondebruin.nl/code.htm

Change the sheet/range to yours
No problem if the range is bigger because it test for a valid mail address in each cell


The code will display the mail first so you can see if it is correct
You can change this line to .Send if it is working OK if you want