Automatic attachment and email to be sent

Jaiguns63

New Member
Dear Expert,
I need an help from you. I need a VBA code from you for the below scenario.
Currently I am doing it manually and i want to automate it.

Current Scenario: I am from invoicing Team and i Generate multiple invoices for the day. I store all these Invoices in PDF mode and save it under my local drive folder or in a desktop folder. Like wise I generate invoice, save pdf invoice copies under local drive for all invoices. Assuming I generate and save 100 pdf copies and currently I send all these 100 pdf invoice copies to 100 recipients/mail address with new 100 emails individually/separately. It is time consuming for me to send all these 100 emails one by one to each different mail address.

Automation Required: I want to avoid this manual work and to atomize the same. My idea is I want to run a macro which will send all pdf copies to required email address using outlook.

For this Once invoice copies printed and saved in local drive, I will maintain a excel database with invoice number, email address id, Signature as "Invoicing Team" columns with details entered in to it. Using this excel details, macro should run for each invoice numbers like open a black outlook email, entered the subject which is same off attachment of that particular pdf invoice copy, body of the email should be "invoice copy" and signature should be a default one as "invoicing Team".

Please help me with a VBA code for applying above scenarios.
Your help is much appreciated.


Thanks
Jay
 

DanteAmor

Well-known Member
Hi Jay:
Then you will have an Excel sheet with this information:

Sheet1
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:154.93px;" /><col style="width:161.58px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >invoice number</td><td >email address</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">123</td><td style="color:#0000ff; text-decoration:underline; ">damor@gmail.com</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">124</td><td style="color:#0000ff; text-decoration:underline; ">email@yahoo.com</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">125</td><td style="color:#0000ff; text-decoration:underline; ">jay@hotmail.com</td></tr></table>

-------------
If in cell A2 you have the number 123, what is the pdf file called? maybe 123.pdf
For this to work automatically, you must specify what data you have on the sheet and how it is in the folder.
Also if you can specify the name of the folder.


Another important information is the body of the mail, is it always the same text?
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top