Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default send email to each customer email in excel sheet.

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,

--

-keevill-

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,123
Default send email to each customer email in excel sheet.

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"-keevill-" wrote in message ...
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,

--

-keevill-

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11
Default send email to each customer email in excel sheet.

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


"Ron de Bruin" wrote in message
...
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





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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"-keevill-" wrote in message
...
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,

--

-keevill-


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,123
Default send email to each customer email in excel sheet.

Hi

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


See
http://www.rondebruin.nl/code.htm


For Each cell In ThisWorkbook.Sheets("Sheet1").Range("A1:A100")


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

.Display 'or use .Send





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"-keevill-" wrote in message ...
I am afraid I need more help ... pls see below...my questions..


"Ron de Bruin" wrote in message
...
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





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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"-keevill-" wrote in message
...
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,

--

-keevill-


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
send 1 sheet of a workbook in email - Excel 07 Kim K Excel Discussion (Misc queries) 10 July 10th 07 05:26 PM
I cant send via email my excel sheet. ERROR always. piglets Excel Discussion (Misc queries) 0 June 6th 07 07:00 PM
How to send email to a list of people in excel sheet? David Excel Discussion (Misc queries) 1 December 14th 06 01:46 AM
body of email disappears when I send an email from Excel ~A Excel Discussion (Misc queries) 0 February 25th 05 11:55 PM
Am not getting "send" option to email Excel sheet Pegin Excel Excel Discussion (Misc queries) 0 February 2nd 05 09:43 PM


All times are GMT +1. The time now is 09:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"