send email from excel 2010 based on user selected cells

I have users with company gas cards.  every month we need to send an email to each user with their current charges along with their due date.  Currently my finance person uses the attached excel sheet (names changed to protect the innocent and not so innocent) and creates an email for each person one at a time.  I have created an Outlook template that helps but it would really help if I could automate the process to a command button to send the email with the current information included in the body of the email based on the following:
email address (to line)
subject Gas Charges
body
     "Good afternoon,

This is to inform you that your personal gas charges for (previous month/year) are in the amount of BDS$ (from cell) or USD$(convert to USD based on conversion rate in sheet2.A2)   . Please make payment on or before (current month) 20, (current year).
User-Accounting.xls
sptechAsked:
Who is Participating?
 
SteveCommented:
The following code is a starting point for you based upon your question description:

Sub Mail_Charges()
Dim OutApp As Object
Dim OutMail As Object
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

If ActiveCell.Value Like "*Charges" Then

For x = 2 To Range("A65526").End(xlUp).Row
    mAddress = Cells(x, "B")
    
    mBody = "Good Afternoon " & Cells(x, "A")
    mBody = mBody & vbCrLf
    mBody = mBody & "This is to inform you that your personal gas charges for " & Format(Now(), "mmmm yyyy")
    mBody = mBody & " are in the amount of BDS$ " & Format(Cells(x, ActiveCell.Column), "0.00")
    mBody = mBody & " or USD$ " & Format(Cells(x, ActiveCell.Column) * Sheets(2).Cells(2, "A"), "0.00") & "."
    mBody = mBody & vbCrLf
    mBody = mBody & vbCrLf
    mBody = mBody & "Please make payment on or before " & Format(Now(), "mmmm 20 yyyy")
    Debug.Print mBody
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = mAddress
        '.CC = ""
        '.BCC = ""
        .Subject = "Gas Charges"
        .Body = mBody
        .Send
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
    
Next x

With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

Else

MsgBox "Select a cell with Charges"

End If

End Sub

Open in new window


If you run the code while selecting a charge column header, it will mail the details for that column.
This is just a starting point to complete your task, please comment any changes you require to the code or any explanations.

ATB
Steve.
C--Users-shall-Desktop-User-Accounting.x
0
 
Rob HensonFinance AnalystCommented:
You can do this with MS Word Mail Merge function.

Create your basic email/letter in Word and then link it to the Excel file for the data, the Merge Wizard will use the column headers as Merge Fields for inserting into the email.

Looking at the file though, you may have to re-arrange your data into a data list, ie with multiple rows per user and just three columns of data: Month, Charge Amount and Payment Amount. The Merge wizard will also allow a filter on current month so you don't end up with repeated emails.

Thanks
Rob H
0
 
Robberbaron (robr)Commented:
the other feature you can use is HTML format for the message.

that way you can have bold, hr etc,
const olMailItem = 0
const olFormatHTML = 2

Set outmsg = OutApp.CreateItem(olMailItem)
outmsg .IsBodyHtml = true;
outmsg .Body = "std text";
                outmsg .HTMLBody = "<br>";
                bodyHTML = "<p style='color:darkblue'><br> this is my HTML formated <i>email</i> <br />";
                bodyHTML = " The links will work for 4 days only</p><hr/>";

   outMsg.HtmlBody =  outMsg.HtmlBody + bodyHTML

       outMsg.Send
0
 
sptechAuthor Commented:
This is the start I have been looking for.  Thank you!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.