Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel 2007 Email Macro

Status
Not open for further replies.

trussman

Technical User
Feb 19, 2004
142
US
I tried this in the Office thread and was told to start it here.

I am looking for code to take the emails listed in a column in Excel that would automatically take all those emails and put them into one email.

Example:

Email Address Name Company
myemail@mydomain.com Joe Schmoe ABC Architects
youremail@mydomain.com Jane Doe XYZ Designers
heremail@theirdomain.com Mary Jane Jane's Designs

Email address 'To' line: myemail@mydomain.com; youremail@mydomain.com; heremail@theirdomain.com

I have a list of 60+ emails in this Excel file. I would like to hit a button that would open a new email and insert all these addresses in the 'To' box.

I am using Office 2007 for everything.

Thanks for any help.
 
First, you'll want to add Outlook to your references in the VBE. Then, you can create an Outlook Application object and create a new mail message with:

Code:
Dim ol as Outlook.Application
Set ol = CreateObject("Outlook.Application")

Dim newMessage as Outlook.MailItem
Set newMessage = ol.CreateItem(olMailItem)

Then you can loop through the excel file and add recipients (this assumes they are in column A):
Code:
Dim rec as Outlook.Recipient
Dim y as Integer
y=2
Do While Range("a" & y).Value <> ""
    Set rec = newMessage.Recipients.Add(Range("a" & y).Value)
    y=y+1
Loop

Finally, display the message:
Code:
newMessage.Display


This might need some tinkering, as I just kinda made it up off the top of my head, but it should be a good jumping off point for you.


-V
 
Thanks for the help. I may be back. I am not much in writing scripts and programs. So this will be an experience for me.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top