Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Sending an email using VBA in excel

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 ...

  1. #1
    New Member
    Join Date
    May 2004
    Posts
    27

    Default Sending an email using VBA in excel

    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.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default Re: Sending an email using VBA in excel

    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    New Member sxs9999's Avatar
    Join Date
    Apr 2004
    Location
    England
    Posts
    44

    Default

    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

  4. #4
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default Re: Sending an email using VBA in excel

    JSW: Try and try again: "The way of the Coder!"

  5. #5
    Board Regular
    Join Date
    Aug 2004
    Posts
    60

    Default Re: Sending an email using VBA in excel

    Does anyone know how to do this with Lotus Notes insead of Outlook??

    Thank you

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default Re: Sending an email using VBA in excel

    Sending mail through Lotus has been asked too... just try a search using 'Lotus Notes' as part of your query.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  7. #7
    Board Regular
    Join Date
    Aug 2004
    Posts
    60

    Default Re: Sending an email using VBA in excel

    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???

  8. #8
    New Member
    Join Date
    Oct 2013
    Posts
    4

    Default Re: Sending an email using VBA in excel

    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.

  9. #9
    New Member
    Join Date
    Mar 2012
    Posts
    2

    Default Re: Sending an email using VBA in excel

    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 by SureShotUK; Oct 23rd, 2013 at 01:01 PM.

  10. #10
    Board Regular
    Join Date
    Mar 2008
    Location
    Stockholm, Sweden
    Posts
    216

    Default Re: Sending an email using VBA in excel

    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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com