Take a look here:
This is a discussion on Sending an email using VBA in excel within the Excel Questions forums, part of the Question Forums category; Hi- I am trying to write some VBA code in Excel to do the following task. I want to create ...
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.
This is the code I use :
You can attach this to a button or other event
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
aEmail.Importance = 2
aEmail.Subject = "Indicator activity warning ( TestMailSend )"
'Set Body for mail
aEmail.Body = "Please log onto the MIS v2 system to check status (( Indicator List))"
aEmail.To = strRecipients
'or send one off to 1 person use this static code
Hope it Helps.
Look at this:
JSW: Try and try again: "The way of the Coder!"
Does anyone know how to do this with Lotus Notes insead of Outlook??
Sending mail through Lotus has been asked too... just try a search using 'Lotus Notes' as part of your query.
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???
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.
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.
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
E = Range("B3").Value
S = Range("B6").Value
Application.DisplayAlerts = False
For Z = 1 To E
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")
Set newemail = openol.CreateItemFromTemplate("C:\Users\This will be your computer user name\AppData\Roaming\Microsoft\Templates\Email Template.oft")
On Error Resume Next
.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
AttCol = AttCol + 1
Set openol = Nothing
Set newemail = Nothing
S = S + 1
Application.DisplayAlerts = True
Last edited by SureShotUK; Oct 23rd, 2013 at 12:01 PM.
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?