Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / December 2007

Tip: Looking for answers? Try searching our database.

Sending one mail to addresses from Excel file

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
johnie@johnie.com - 22 Dec 2007 09:45 GMT
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
Ron de Bruin - 22 Dec 2007 12:24 GMT
Hi John

Do you want to send only text or a file ?

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
johnie@johnie.com - 22 Dec 2007 12:55 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.