sptech
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(olMailIt
outmsg .IsBodyHtml = true;
outmsg .Body = "std text";
outmsg .HTMLBody = "<br>";
bodyHTML = "<p style='color:darkblue'><br
bodyHTML = " The links will work for 4 days only</p><hr/>";
outMsg.HtmlBody = outMsg.HtmlBody + bodyHTML
outMsg.Send
that way you can have bold, hr etc,
const olMailItem = 0
const olFormatHTML = 2
Set outmsg = OutApp.CreateItem(olMailIt
outmsg .IsBodyHtml = true;
outmsg .Body = "std text";
outmsg .HTMLBody = "<br>";
bodyHTML = "<p style='color:darkblue'><br
bodyHTML = " The links will work for 4 days only</p><hr/>";
outMsg.HtmlBody = outMsg.HtmlBody + bodyHTML
outMsg.Send
ASKER
This is the start I have been looking for. Thank you!!!
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