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
     "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).
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
Next x

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


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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.

Rob H
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

sptechAuthor Commented:
This is the start I have been looking for.  Thank you!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.