Sending one mail to addresses from Excel file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an excel file with email addresses. Periodically I need to send all
of these these people an email, so I need one mail where the 'To' field is
filled with all the email addresses. What is the best way to automate this?
Can I perhaps make a macro in Excel that opens Outlook and creates the mail?
Thanks for any help.
John
 
It could be only text or text and a file. Though I don't mind adding the
text and file manually.
Thinking about it, some kind of procedure that puts every email address
together, separated by a semi colon, would enable me to past all the
addresses in the mail in one action.
Thanks,
John
 
Hi John

Use a example from my site and click on the tips link that you find on every page
http://www.rondebruin.nl/sendmail.htm


Try this one for Outlook for only text

Sub Mail_small_Text_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"

On Error Resume Next
For Each cell In ThisWorkbook.Sheets("Sheet1") _
.Range("A1:A10").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next cell
On Error GoTo 0
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
.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub
 
Oops

The mail addresses must be in A1: A10 of a sheet named "Sheet1" in this example
 
This works great! Thank you.

Is it possible to alter this routine so that it will capture all email
addresses from A1 to the end of the file (in other words till it encounters
a line where all cells are empty)?

John
 
Hi John

It use SpecialCells(xlCellTypeConstants)

It will only loop through all cells with data and test that cell if there is a mail address
You can change A1:A10 to A1:A1000 and it will only loop through the cells with data in it.
 
Got it. I thought increasing the range would influence the performance time,
but it doesn't, so this solution is fine.

Thanks again for your help.

John
 
Back
Top