Sending an email using VBA in excel

DanR1245

New Member
Joined
May 12, 2004
Messages
27
Hi-

I am trying to write some VBA code in Excel to do the following task.

I want to create a button in excel that upon the user clicking, an automatic email using Outlook is sent to a recipient. The name of the recipient varies as well as some of the text in the body of the email. However, each of these fields is a cell in the excel worksheet.

I haven't done much VBA coding in Outlook, and I don't even know if this is possible.

Any help is much appreciated.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
This is the code I use :

You can attach this to a button or other event

Sub SetRecipients()
Dim aOutlook As Object
Dim aEmail As Object
Dim rngeAddresses As Range, rngeCell As Range, strRecipients As String

Set aOutlook = CreateObject("Outlook.Application")
Set aEmail = aOutlook.CreateItem(0)
'set sheet to find address for e-mails as I have several people to mail to
Set rngeAddresses = ActiveSheet.Range("A3:A13")
For Each rngeCell In rngeAddresses.Cells
strRecipients = strRecipients & ";" & rngeCell.Value
Next
'set Importance
aEmail.Importance = 2
'Set Subject
aEmail.Subject = "Indicator activity warning ( TestMailSend )"
'Set Body for mail
aEmail.Body = "Please log onto the MIS v2 system to check status (( Indicator List))"
'Set attachment
aEmail.ATTACHMENTS.Add ActiveWorkbook.FullName
'Set Recipient
aEmail.To = strRecipients
'or send one off to 1 person use this static code
'aEmail.Recipients.Add "E-mail.address-here@ntlworld.com"
'Send Mail
aEmail.Send

End Sub

Hope it Helps.
SXS
 
Upvote 0
I have done that but I need a little more assistance.
I want the macro to copy what is in excel (a certain range of cells) and copy it into the email. I can not seem to find the paste function to paste the copy into the body of the email. Can someone help with this???
 
Upvote 0
So i have been using the above VBA for sending off emails. But how can i make it insert yesturdays date in the BODY of the email? I am a noob when it comes to vba but learning.
 
Upvote 0
I have been struggling with this for the last week or so and I have the following code which so long I have a Template Email (called Template Email.oft) already saved on my PC and Outlook is running when I try to run the Macro I can choose varying parameters for the email such as :
Subject, To, CC, BCC, Text in the email and I can also add up to 5 attachments.

I need to have two sheets to do this one contains the details I want for each email I wish produce (this is called "Email List"), and I have another sheet called "Control" which uses a formula to determine (E) how many emails there are in the list ( =counta(XXX) ) and another formula to determine (S) the row of the first email I wish to send. I have done this because in my email list there are some emails at the top of the list that I only want to send on certain days so I may want to start at the email on row 4 rather than on row 2.

The code then checks the various parameters to those listed on the current row for the email in question. It then opens a MessageBox displaying the details of the email it is about to send and asks if you want to send it. If you click No it looks at the next email in the list and opens a new messagebox with it's details. If you clicked yes then it opens the template email (with any signature you might have in it) and overwrites the details into it except for the body of the email where it only inserts your text any other text that is there as part of the template, so you retain the signature, it then sends the email before moving onto the next email in the list.

I must say a big thank you to Ron De Bruin and his excellent site Mail from Excel example pages without which I would not have completed this, but I thought I would post my code as it may be useful for others to use. BTW I am sure others will be able to clean the code up a little but I am still only really beginning with VBA!
This code can be copied directly into a new module to give you a new macro called SendEmail.

Sub SendEmail()

Dim openol As Object
Dim newemail As Object 'Defines the name handle of the email template we will use

Dim A As Integer 'A is the number of attachments counted in the worksheet "Email List"
Dim AttCol As Integer 'AttCol is the current column number for the attachment to be attached to the email _
this value will be between 8 and 12 for columns H through L

Dim SubName As String 'Taken from cell values in column C in the worksheet "Email List" (Subject Name)
Dim ToName As String 'Taken from cell values in column D in the worksheet "Email List"
Dim CCName As String 'Taken from cell values in column E in the worksheet "Email List"
Dim BCCName As String 'Taken from cell values in column F in the worksheet "Email List"
Dim AttName As String 'Taken from the columns H to L in the worksheet "Email List". AttName is the _
filepath and name of any attachment.

Dim BodyText As String 'Taken from cell values in column M in the worksheet "Email List"
Dim SendEmail As Integer 'SendEmail is the MessageBox response for sending the email
Dim MAttName As String 'MAttName is the name for the Attachments used in the message box _
taken from cell values in column N in the worksheet "Email List"

Dim E As Integer 'E is the number of emails to send taken from a formula in the control sheet _
which Counts Number of emails in Email List. Value is in "Control" worksheet in Cell B3
Dim S As Integer 'S is the starting row number containing the email data on the "Email List" worksheet _
but this value is taken from cell B6 in the "Control" sheet

Sheets("Control").Select
E = Range("B3").Value
S = Range("B6").Value
Application.DisplayAlerts = False

For Z = 1 To E
Sheets("Email List").Select
SubName = Range("C" & S).Value
ToName = Range("D" & S).Value
CCName = Range("E" & S).Value
BCCName = Range("F" & S).Value
A = Range("G" & S).Value
AttCol = 8
BodyText = Range("M" & S).Value
MAttName = Range("N" & S).Value

SendEmail = MsgBox("Do you want to send the following Email?" _
& vbNewLine & vbNewLine & "Subject: " & SubName & vbNewLine & vbNewLine & _
"To: " & ToName & vbNewLine & _
"CC: " & CCName & vbNewLine & "BCC: " & BCCName & vbNewLine & vbNewLine & _
"With the following Attachments: " & vbNewLine & MAttName, _
vbQuestion + vbYesNo, "Send Email")

If SendEmail = vbYes Then

Set openol = CreateObject("Outlook.Application")
openol.Session.Logon
Set newemail = openol.CreateItemFromTemplate("C:\Users\This will be your computer user name\AppData\Roaming\Microsoft\Templates\Email Template.oft")
On Error Resume Next

With newemail
.display
.To = ToName
.CC = CCName
.BCC = BCCName
.Subject = SubName
.HTMLBody = "" & BodyText & "
" & .HTMLBody
If A > 0 Then
For Y = 1 To A
AttName = Cells(S, AttCol).Value
.Attachments.Add AttName
AttCol = AttCol + 1
Next Y
End If
.send
End With

Set openol = Nothing
Set newemail = Nothing

Else
End If
S = S + 1
Next Z

Sheets("Control").Select
Application.DisplayAlerts = True

End Sub
 
Last edited:
Upvote 0
In Outlook, is there a way to automatically move the email that you send to a folder of your own choice, so that you don't end up with all your Excel-generated mail in the "Sent"-folder? My office has recently changed to Outlook, but before that, we used Lotus Notes, and through the Notes API this was possible. Is it possible in Outlook too?

Bengt
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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
Back
Top