Hi, I own an online business in which people buy a service that is given to others as a gift.
When people make a purchase, in my shopping cart software I ask them things like the recipient’s name, gender, etc. and I currently copy and paste some of that information into some CRM (Customer Relationship Management) software that I have, which I then use to send a personalized email.
For some other reasons, I have an Excel spreadsheet where I make an ODBC connection to my shopping cart software and each order becomes a row in the spreadsheet.
What I’d like to do is send out the personalized email using the Excel spreadsheet instead. I would basically want to “mail merge” some of the information that I download into the spreadsheet (like the recipient name) to some standard formatted email text thet I have.
Just to be clear, I don’t want to mail-merge the whole spreadsheet, just an individual row at a time as I get new orders.
I’m willing to write some VBA code. Any ideas or places to start? Thanks
I have done something like this but I don’t have the file on this computer. Excel VBA has a sendmail method, but that allows you to just send a workbook as an attachment. You can’t do what you want with that. IIRC I used VBA to call methods in Outlook. Also IIRC Excel doesn’t have a built-in SMTP client; this works because Outlook also supports VBA. If you wanted to use some other mail client I don’t know if it’s possible.
You might Googling around for “Excel Outlook VBA send email”. I also find www.ozgrid.com a great resource for Excel and VBA.
Meantime, when I get home tonight I’ll see if I can find the file where I did this. We had weekly status meetings and would take turns giving presentations on various topics each week. I used Excel to keep track of who was scheduled for what each week and used it to automatically notify people that they were scheduled to give a presentation at a meeting one week in advance. So I know it’s possible.