Send Outlook Emails via Excel

tommychowdah

New Member
Joined
Dec 26, 2017
Messages
31
Hi Everyone,

I am trying to send out outlook emails via an excel spreadsheet. Below is the code I am currently using. I have a table with the individual's name, email address, and business as well as a subject line and CC column (example of the table is below). I also have a text box created where the Dear [Name] and Business [Name] is replaced with the column values. When I hit the macro button, the code runs through each row in the table and an email pops up in outlook. At this point, I was hoping to layer in some additional functionality and am curious if anyone has any suggestions. Any help is much appreciated.

1. Ability to reply to the last email sent or received for each individual in the table
2. Ability to delay send
3. Ability to drop in outlook signature
4. Ability to format hyperlinks

NameEmailSubjectCCBusiness
Tomtom@tom.comSample Subjectbob@bob.com123 Company
Jimjim@jim.comSample Subjecttom@tom.comABC Company
Bobbob@bob.comSample Subjectjim@jim.comXYZ Company

Sub send_email()

Dim i As Integer
Dim name, email, body, subject, copy, place, business As String
Dim OutApp As Object
Dim OutMail As Object

body = ActiveSheet.TextBoxes("TextBox 1").Text

i = 2
'Loop down name column starting at row 2 column 1
Do While Cells(i, 1).Value <> ""

name = Split(Cells(i, 1).Value, " ")(0) 'extract first name
email = Cells(i, 2).Value
subject = Cells(i, 3).Value
copy = Cells(i, 4).Value
business = Cells(i, 5).Value

'replace place holders
body = Replace(body, "X1", name)
body = Replace(body, "X2", business)

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = email
.CC = copy
.subject = subject
.body = body
'.Attachments.Add ("") 'You can add files here
.Display '.Send
End With

'reset body text
body = ActiveSheet.TextBoxes("TextBox 1").Text

i = i + 1
Loop

Set OutMail = Nothing
Set OutApp = Nothing

End Sub
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,215
Messages
5,836,053
Members
430,403
Latest member
RoToR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top