
Signature
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
>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
>
[quoted text clipped - 7 lines]
>> Thanks for any help.
>> John
Ron de Bruin - 22 Dec 2007 13:26 GMT
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

Signature
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
> It could be only text or text and a file. Though I don't mind adding the
> text and file manually.
[quoted text clipped - 15 lines]
>>> Thanks for any help.
>>> John
Ron de Bruin - 22 Dec 2007 13:29 GMT
Oops
The mail addresses must be in A1: A10 of a sheet named "Sheet1" in this example

Signature
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
> Hi John
>
[quoted text clipped - 65 lines]
>>>> Thanks for any help.
>>>> John
johnie@johnie.com - 22 Dec 2007 15:29 GMT
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
> Oops
>
[quoted text clipped - 71 lines]
>>>>> Thanks for any help.
>>>>> John
Ron de Bruin - 22 Dec 2007 15:49 GMT
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.

Signature
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
> This works great! Thank you.
>
[quoted text clipped - 79 lines]
>>>>>> Thanks for any help.
>>>>>> John
johnie@johnie.com - 22 Dec 2007 16:19 GMT
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
> Hi John
>
[quoted text clipped - 88 lines]
>>>>>>> Thanks for any help.
>>>>>>> John