Send Email at the press of a button
Results 1 to 4 of 4

Thread: Send Email at the press of a button
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2013
    Posts
    1,159
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Send Email at the press of a button

    I have a sheet that is updated daily. There are three sections that are updated. Once a section is updated I would like to press a button that will send an email out to one of three people dependant on the button or selection i make. For example, If I have update the sheet relevant to person A then I want to select person A and an email will be sent notifying them that there information has been updated. Really hope someone can help with this. Thank you.

  2. #2
    MrExcel MVP RickXL's Avatar
    Join Date
    Sep 2013
    Location
    UK North Midlands
    Posts
    4,314
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Send Email at the press of a button

    Hi, rather than re-invent the wheel, take a look at Ron de Bruin's site. He covers most things email.
    Mail from Excel with Outlook (VBA)

    For instance:
    Code:
    Sub Mail_small_Text_Outlook()
    'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    'Working in Office 2000-2013
        Dim OutApp As Object
        Dim OutMail As Object
        Dim strbody As String
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
        strbody = "Hi there" & vbNewLine & vbNewLine & _
                  "This is line 1" & vbNewLine & _
                  "This is line 2" & vbNewLine & _
                  "This is line 3" & vbNewLine & _
                  "This is line 4"
    
        On Error Resume Next
        With OutMail
            .To = "ron@debruin.nl"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .Body = strbody
            'You can add a file like this
            '.Attachments.Add ("C:\test.txt")
            .Send   'or use .Display
        End With
        On Error GoTo 0
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    End Sub
    You would need to put that code in a macro module then link it to a button. Come back if you get stuck.
    RickXL

    Excel 2013 and Windows 10

  3. #3
    Board Regular
    Join Date
    Sep 2013
    Posts
    1,159
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Send Email at the press of a button

    Is there a way to edit the font in the sent email (make it bigger).

    Also is there a way of editing the body without having to go into VBA. Auto copy text from a cell for example?

  4. #4
    MrExcel MVP RickXL's Avatar
    Join Date
    Sep 2013
    Location
    UK North Midlands
    Posts
    4,314
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Send Email at the press of a button

    Hi,

    I think it uses whatever font you have set as default.
    You could create a template and save the new size in there or you could use HTML. In which case you would need to change .Body = strBody into .HTMLBody = strBody like this other example from Ron de Bruin: Insert Outlook Signature in mail
    If you want to use data from the worksheet in the body then just assign the the string to the cell contents. For example:
    Code:
    strBody = Worksheets("Sheet1").Range("A2").Value
    RickXL

    Excel 2013 and Windows 10

Some videos you may like

User Tag List

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
  •