Send Email at the press of a button

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,159
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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
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.
 

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,159
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?
 

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
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
 

Watch MrExcel Video

Forum statistics

Threads
1,096,458
Messages
5,450,573
Members
405,619
Latest member
mjh237

This Week's Hot Topics

Top