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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

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,102,774
Messages
5,488,770
Members
407,657
Latest member
Sassy5

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top