{"id":44,"date":"2023-10-08T20:15:00","date_gmt":"2023-10-08T17:15:00","guid":{"rendered":"https:\/\/fsaeed.blog\/?p=44"},"modified":"2026-10-31T00:18:54","modified_gmt":"2026-10-30T21:18:54","slug":"vba-excel-row-to-pdf-email","status":"publish","type":"post","link":"https:\/\/fsaeed.blog\/index.php\/2023\/10\/08\/vba-excel-row-to-pdf-email\/","title":{"rendered":"VBA: Turn Every Excel Row into a PDF and Email It"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Hello again, tech enthusiasts! \ud83d\udc4b<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Imagine you have a list of 40 customers. Each one needs a statement: their name, their invoice number and the amount they owe. Today you fill a template by hand, save it as PDF, open Outlook, attach the file and type the email. Forty times. Your coffee gets cold.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In this post, we will write a small <strong>VBA<\/strong> macro to do it for you. VBA (Visual Basic for Applications) is the programming language built into Excel. A macro is a small program that clicks and types for you, much faster than a person.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">We will be careful too. The macro will <strong>show<\/strong> each email first instead of sending it. You check it, and you click Send.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">What you need<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Excel and Outlook (the desktop apps) on Windows<\/li>\n\n\n<li>A workbook saved as <code>.xlsm<\/code> (macro-enabled)<\/li>\n\n\n<li>A sheet called <code>Data<\/code> with your list<\/li>\n\n\n<li>A sheet called <code>Template<\/code> with your nice design<\/li>\n\n\n<li>A folder for the PDFs, for example <code>C:\\Reports\\Statements<\/code><\/li>\n\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Step 1: Prepare the Data sheet<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">In the <code>Data<\/code> sheet, put headers in row 1 and one customer per row:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Column A: Customer Name<\/li>\n\n\n<li>Column B: Email<\/li>\n\n\n<li>Column C: Invoice Number<\/li>\n\n\n<li>Column D: Amount<\/li>\n\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Use test data first, like <code>customer1@example.com<\/code>. Never test with real customers.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 2: Prepare the Template sheet<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Design the <code>Template<\/code> sheet the way you want the PDF to look. Leave empty cells for the values. In this example:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>B3<\/code> = customer name<\/li>\n\n\n<li><code>B4<\/code> = invoice number<\/li>\n\n\n<li><code>B5<\/code> = amount<\/li>\n\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Also set the print area: select your design, then go to <strong>Page Layout<\/strong> &gt; <strong>Print Area<\/strong> &gt; <strong>Set Print Area<\/strong>. The PDF will include only this area.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 3: Add the macro<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Press <code>Alt + F11<\/code> to open the VBA editor. Click <strong>Insert<\/strong> &gt; <strong>Module<\/strong> and paste this code:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Option Explicit\n\nSub CreatePdfsAndEmails()\n    Dim wsData As Worksheet, wsTpl As Worksheet\n    Dim outApp As Object, mail As Object\n    Dim lastRow As Long, r As Long\n    Dim folder As String, pdfPath As String, safeName As String\n\n    Set wsData = ThisWorkbook.Worksheets(\"Data\")\n    Set wsTpl = ThisWorkbook.Worksheets(\"Template\")\n    folder = \"C:\\Reports\\Statements\\\"   ' must end with a backslash\n\n    ' Find the last used row in column A\n    lastRow = wsData.Cells(wsData.Rows.Count, \"A\").End(xlUp).Row\n\n    ' Start Outlook (or connect to it if it is already open)\n    Set outApp = CreateObject(\"Outlook.Application\")\n\n    For r = 2 To lastRow\n        ' 1. Fill the template with this row's values\n        wsTpl.Range(\"B3\").Value = wsData.Cells(r, \"A\").Value\n        wsTpl.Range(\"B4\").Value = wsData.Cells(r, \"C\").Value\n        wsTpl.Range(\"B5\").Value = wsData.Cells(r, \"D\").Value\n\n        ' 2. Save the template as a PDF\n        safeName = Replace(wsData.Cells(r, \"C\").Value, \"\/\", \"-\")\n        pdfPath = folder &amp; \"Statement_\" &amp; safeName &amp; \".pdf\"\n        wsTpl.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfPath, _\n            Quality:=xlQualityStandard, OpenAfterPublish:=False\n\n        ' 3. Create the email and attach the PDF\n        Set mail = outApp.CreateItem(0)   ' 0 = a new mail item\n        With mail\n            .To = wsData.Cells(r, \"B\").Value\n            .Subject = \"Your statement - Invoice \" &amp; wsData.Cells(r, \"C\").Value\n            .Body = \"Dear \" &amp; wsData.Cells(r, \"A\").Value &amp; \",\" &amp; vbCrLf &amp; vbCrLf &amp; _\n                    \"Please find your statement attached.\" &amp; vbCrLf &amp; vbCrLf &amp; _\n                    \"Best regards\"\n            .Attachments.Add pdfPath\n            .Display   ' show the email; change to .Send only when you are sure\n        End With\n        Set mail = Nothing\n    Next r\n\n    MsgBox \"Done! \" &amp; (lastRow - 1) &amp; \" PDFs created.\", vbInformation\nEnd Sub<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Step 4: Understand the important lines<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>End(xlUp)<\/code> is like pressing <code>Ctrl + Up<\/code> from the bottom of the sheet. It finds the last row with data, so the macro works for 5 rows or 500.<\/li>\n\n\n<li><code>ExportAsFixedFormat<\/code> is the same as <strong>File<\/strong> &gt; <strong>Save As<\/strong> &gt; <strong>PDF<\/strong>, but in code.<\/li>\n\n\n<li><code>CreateObject(\"Outlook.Application\")<\/code> opens a connection to Outlook. We do not need any special references with this method.<\/li>\n\n\n<li><code>.Display<\/code> opens each email in a window. Nothing leaves your computer until you click <strong>Send<\/strong>.<\/li>\n\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Step 5: Run it<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Close the editor. In Excel, press <code>Alt + F8<\/code>, choose <code>CreatePdfsAndEmails<\/code> and click <strong>Run<\/strong>. Check the folder for PDFs, and check each email window.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When you trust the result, you can change <code>.Display<\/code> to <code>.Send<\/code>. Do this only after many good test runs. A sent email cannot come back.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Common problems<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>&#8220;Run-time error 1004&#8221; on ExportAsFixedFormat.<\/strong> \u2192 The folder does not exist, or the file name has a bad character like <code>\/<\/code> or <code>:<\/code>. Create the folder and clean the name, like we did with <code>Replace<\/code>.<\/li>\n\n\n<li><strong>&#8220;ActiveX component can&#8217;t create object.&#8221;<\/strong> \u2192 Outlook desktop is not installed. This method needs the classic desktop Outlook.<\/li>\n\n\n<li><strong>The PDF is empty or cut in half.<\/strong> \u2192 Set the print area on the Template sheet and check <strong>Page Layout<\/strong> &gt; <strong>Fit to 1 page<\/strong>.<\/li>\n\n\n<li><strong>Outlook shows a security warning.<\/strong> \u2192 This is Outlook protecting you. Keep your antivirus up to date and do not turn the warning off; just click <strong>Allow<\/strong> when you started the macro yourself.<\/li>\n\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Wrap-up<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">With about 40 lines of code, a one-hour job becomes a two-minute job. Your template stays pretty, and every email is checked before it goes.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next, try adding a column called <code>Sent<\/code> and let the macro write the date there after each email. Then you always know who got their statement. Have fun!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Use a short VBA macro to create one PDF for each row in your sheet and prepare an Outlook email for each one.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[7,5,10,11,9],"class_list":["post-44","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-automation","tag-excel","tag-outlook","tag-pdf","tag-vba"],"_links":{"self":[{"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/posts\/44","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/comments?post=44"}],"version-history":[{"count":1,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/posts\/44\/revisions"}],"predecessor-version":[{"id":89,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/posts\/44\/revisions\/89"}],"wp:attachment":[{"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/media?parent=44"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/categories?post=44"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/tags?post=44"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}