Send Emails through Outlook using Email Addresses from Excel and text from Word

This macro allows you to send an email to a list of recipients through excel. The email will be sent through Outlook and the list of recipients is in excel. The message or body of the email is a word document located anywhere on your computer. The entire macro is run through excel and the email addresses used are listed in excel.

When you run the macro, you will select a word document from your computer to be the body of the email and then from there, the macro will send an email through Outlook to all emails listed in excel. The emails MUST be listed vertically; they must be in individual cells but only in one column, going up and down.

IMPORTANT
You need two defined names for this macro to work:
subjectcell will be the name of the cell that contains the title of the email.
tolist should be the name of the first cell in a column where the email addresses are located. This cell should be the first email address in a vertical list with all other emails listed below that one in the column.
Send an email through Outlook using text from Word

Sub SendOutlookMessages()

'This macro will send an email through Outlook to a list of
'recipients whose emails are in excel. The body of the email comes
'from a word document which you will choose from your computer
'
Dim OL As Object, MailSendItem As Object
Dim W As Object
Dim MsgTxt As String, SendFile As String
Dim ToRangeCounter As Variant
SendFile = Application.GetOpenFilename(Title:="Select MS Word " & _
"file to mail, then click 'Open'", buttontext:="Send", _
MultiSelect:=False)
Set W = GetObject(SendFile)
MsgTxt = W.Range(Start:=W.Paragraphs(1).Range.Start, _
End:=W.Paragraphs(W.Paragraphs.Count).Range.End)
Set W = Nothing
Set OL = CreateObject("Outlook.Application")
Set MailSendItem = OL.CreateItem(olMailItem)
ToRangeCounter = 0
For Each xCell In ActiveSheet.Range(Range("tolist"), _
Range("tolist").End(xlDown))
ToRangeCounter = ToRangeCounter + 1
Next xCell
If ToRangeCounter = 256 Then ToRangeCounter = 1
With MailSendItem
.Subject = ActiveSheet.Range("subjectcell").Text
.Body = MsgTxt
For Each xRecipient In Range("tolist").Resize(ToRangeCounter, 1)
RecipientList = RecipientList & ";" & xRecipient
Next xRecipient

.To = RecipientList
.Send
End With
Set OL = Nothing
End Sub

I think I have all but cannot understand how to do it?? in excel coz not familiar with VBA programming can anyone help????

Hello,

I have a report that needs to be run daily. I have to then have to send an email to the individuals that are responsible for the areas that the report has flagged. There are 75 total possible people to send to, but not all get an email each day. Looking for a way to save some time.

Is there a way I can correlate the location name to each person's email that is responsible for that location and the have excel create the outlook mail message and send that to each person.

Any idea's would be greatly appreciated.

Thanks,

My goal is to create a macro in Excel to produce an email in Outlook to all the email addresses listed in column I in one email in the "To:" field.

What I've accomplished in the following VBA is that it creates a separate email for each email address listed in column I.

How do I create just one email for the list of email addresses in column I?

Typing in the email addresses within the VBA is not an option. The list of email addresses change with another macro.

Thank you in advance.

-Erica


Sub SendEmail()

Dim OutlookApp As Outlook.Application
Dim MItem As Outlook.MailItem
Dim cell As Range
Dim Subj As String
Dim EmailAddr As String
Dim Recipient As String
Dim Msg As String

'Create Outlook object
Set OutlookApp = New Outlook.Application

'Loop through the rows
For Each cell In Columns("I").Cells.SpecialCells(xlCellTypeVisible)
If cell.Value Like "*@*" Then

'Get the data
Subj = "This is the Subject Field"
Recipient = cell.Offset(0, -1).Value
EmailAddr = cell.Value

'Compose Message
Msg = Recipient & vbCrLf
Msg = Msg & "Please review the following message."

'Create Mail Item and view before sending
Set MItem = OutlookApp.CreateItem(olMailItem)
With MItem
.To = EmailAddr
.Subject = Subj
.Body = Msg
.Display
End With
End If
Next

End Sub

Hi there. I have a simple spreadsheet (attached) which is designed to monitor when insurance premiums are due for adjustment. The spreadsheet will send an email to the recepient as and when a policy is due for adjustment (i.e. when the date in Column G reaches today's date). However, the spreadsheet is not finished and I need some help on the following:

* How Can I make the email send to multiple recipients? (5 to be exact). Currently there is only one email address (Column B).

* How Can I take the 'Account Name' from Column E (Barclays for example) and put into the body of the email? - So the email will read 'Dear Team, Please chase premium adjustment for ............... Thank you. At present the email is sent without this info and simply states 'Dear Team, please chase premium adjustment. Thank you.'

*Can the email be sent automatically upon opening the spreadsheet? I currently have to run the Macro in order for the email to be sent.

*Is there any way to avoid having to authorise the email to be sent?

I have attached the spreadsheet for ease of use.

Any advice/instructions would be very much appreciated as my knowledge of Excel is limited.

Many thanks.
Graham.

The first two parts of this macro work just fine, it's the closing part of it which doesn't work. The strange thing is, if I isolate the close part of the macro and run it as it's own macro, it will work. However, it will not work as part of a larger macro, or even if called from a higher macro which contained the open portion. Here is my code:

Code:

Sub launch_send_close_outlook()

'launch Outlook
Dim Outlook As Outlook.Application
Set Outlook = CreateObject("Outlook.Application")
Dim ns As Outlook.Namespace
Dim Folder As Outlook.MAPIFolder
Set ns = Outlook.GetNamespace("MAPI")
Set Folder = ns.GetDefaultFolder(olFolderInbox)
Outlook.Explorers.Add Folder
    
    'send email
    Set myOlApp = CreateObject("Outlook.Application")
    Set mailItem = myOlApp.CreateItem(olMailItem)
    Set myRecipient = mailItem.Recipients.Add("name@domain.com")
    mailItem.body = "testing"
    mailItem.Subject = "test email"
    mailItem.Send

'close Outlook
Set Outlook = CreateObject("Outlook.Application")
Outlook.Quit
Set Outlook = Nothing

End Sub

If anyone can explain what I'm doing wrong with the last part, I would appreciate it. Also, if any other part of my code could use some cleaning up, feel free to let me know.

Thanks!

Our organization just moved from GroupWise to Outlook 2007 but we are still on Office 2003.

We have 12,000 associates and I want to e-mail a selection of 1,000 of them. I have their names and e-mail addresses in an Excel workbook.

Might you know an efficient way (non VBA?) to get that list of 1,000 into an Outlook group (or whatever it is called) so I can e-mail them? I'm so new to Outlook I don't yet understand folders and groups and distribution lists. I assume I must first create a group to house the 1,000 email addresses? How do I do that.

Many thanks.

Hi I have number of excel files in one folder. These have in number of columns which have email adresses. Every email adress has a cell to its right where is either "Y" or "N". What I would like to do is to have this email address picked up if there is a "Y" and not when there is "N" and then to add it to the recipient list in Outlook. To me this is very complicated can anyone give me some guidance please?

Thanks

Hi, Happy New Year,
try not to laugh(or cry)
I have set up a spreadsheet which holds data about various golf clubs.
Included in this is their e mail address. I maybe should have tried to set this up in access?????
However, what I want to do is to send individual e mails to selected clubs, ie not the whole list, such that this appears an individual email, not a spammy thing.
So, to clarify, list of clubs with email addresses in a column.
will have to input a column to identify if selected or not?
Then to set up individual e mails, which attach/use a file as the message body, without doing it manually.
Titles are across row 2, with the selected indicator in column A, email address in column B, ie data A3 and B3.