Mail Merge and Spreadsheet Automation Using VBA

[adebusk]

I designed a workbook that basically tracks and helps follow up on internet leads I receive, and in the evolution I've gotten in way over my head. This workbook uses 6 different sheets, titled "Fresh" "2 Days" "5-7 Days" "14 Days" and "30 Days". The lead has an Age on it, which corresponds to the spreadsheet it should sit in (i.e. the new leads sit in "Fresh" for the first day, then they move to 2 day. When they are 2 days old, then I send an email mail merge out, and move them to "5-7 day"). My concept here for a macro is two-fold. First I want the mail merge to be done say, through a button in excel on highlighted fields or fields in sheets that match or exceed age criteria (I don't want day 2 email going out to a 1 day old lead, or a 5-7 day email going out to a 4 day old lead). Secondly, as soon as the merge is done I want the macro to basically move the people who were emailed to the next sheet.

Any ideas on what code like that would look like? Or is this even possible? I've seen some base ideas that show the mail merge can work, as can the moving rows, but I have no idea how to combine these two. I don't know how to code at all.

Thanks for any help anyone can provide.

oops forgot to mention. excel 2010.

 

[General Ledger]

I personally would not use more than one sheet. All the records are the same except for their age. Keep all the records on one sheet and have a column that designates their "Age Category." This way you can sort, search, filter, do anything very easily with all the records or a subset.

Here is a link to a great website about using Excel to send emails with Outlook. Mail from Excel with Outlook (VBA)

I did a project where I sent hundreds of customized emails and attach a PDF. My Excel file had names, email addresses, etc. My macro:
Read records from the Excel file. I had a column that indicated (Yes/No) for each record if an email was to be sent so I could skip records. In your case, this would be equivalent to using Age to identify which records get an email.
Filled in To with the email address from the Excel file, Subject was the same text for all emails, Body that was a mix (concatenation) of text from the Excel file and a common message, etc. on the emails.
Attached the same PDF to each email.
Sent the email.
Updated each record in the Excel file with the date and time stamp of the email that was sent or a message that Outlook could not send the email because of a bad address (not like "*@*.*")

It worked really well. I do have a caution that might only apply under some situations. I forget why but when I ran my macro, the process read one record from the Excel file, created one email, sent one email, updated the record in the Excel file, then read the next record. It took about three seconds for each email. This tied up my computer so I had to find other work to do while the entire project was running.

Best of luck,

G/L