Email Issue - Use VBA Code?

[MCochranKYB]

I make ACH payments to vendors once a week, and each time I have to also email each vendor a remittance detail of which invoice I paid and the amount. This is several emails and I send essentially the same email, except I update the detail to only send that vendor's information that the email is going to.

I believe there has to be an easier way to do this, possibly through a VBA code. I have all of the ACH data (invoice#, amount, vendor name, etc.) in Excel. My ultimate goal would be to send an email to each vendor individually that is listed on the worksheet, and the attachment/body of the email would send them only their data and not the full worksheet.

Does anyone have suggestions on how to go about this, or if it is even possible? I have Outlook for email and using Excel 2013.

Thanks in advance!

 

[RickXL]

Hi and welcome to the MrExcel Message Board,

I once did some work for a company that needed to have an email sent to action the next step in the process. They were tedious because it was just a summary of already existing data but quite a number of things had to be present before it would be approved and actioned.

I too had the job details listed in Excel, so I wrote some code that would create an email if I right-clicked the appropriate change number in the spreadsheet. The code gathered data from a Word document and Excel and created the email then sent it. It saved some time and also greatly reduced the number of errors because all the input documents were separate.

So, yes, it sounds possible.

Things to bear in mind are that you really need some way of making sure that you never re-send an email by running the code again. It may not be a disaster if you did but the recipients will soon get fed up with it!

Things to do:

Decide on an email format that will work in every case.
Locate the data for the email and ensure it is always present (or at least checked prior to sending).
As well as data, you will also need to be able to assign the correct email addresses to each email.
Write some code in Excel that will create the emails and send them.
(I may be able to help with the last bit )

It is basically a Mail Merge process but using emails.

Regards,

 

[MCochranKYB]

Rick,

Thank you for the welcome!

It sounds like the company you did work for had a similar situation. I have to do this task once a week, and I have to figure out how to automate it!

The email format does not have to be a formal email with lots of writing. It could simply be an email with only the subject line saying “ACH Payment Remittance”, and the detail for that supplier in the body of the email or attached. Could possibly link the file name or a worksheet name to the subject line of the email? (Not sure if that’s an option)

The link below is a sample of the data that we would have each time we need to send the emails. In the sample I only have 9 suppliers/email recipients, but I will actually need to send emails to around 150 different recipients so there is normally around 700 rows of data.

http://www.dropbox.com/s/ka6wmvbz3eujiq8/20161216-ACH-Copy.xlsm?dl=0

Data - The column headings would be the same every time, but obviously data would change with new data as we would pay different invoices each time. I highlighted the columns that would be the most important info to send in each email. This can be manipulated however it is needed – deleting columns, adding additional formulas, etc.

Pivot – This is a pivot table of the data tab. I make a pivot table each time I do this, so I kept it in this workbook. If it is easier to write the code based off of the pivot table data, then that works too.

Supplier Email List – This is an example of the email list we would have. I have only listed my own email for all of the recipients because I would like to test it using my email before trying to send to suppliers. For each recipient, I only want them to receive the data associated with their supplier name (not the entire file/data to each one).

I am a beginner when it comes to VBA, so I am in need of major help to write code for a process like this!

Thank you for offering to assist with this, and please let me know if you need any additional information from me!

-Matt