Help w/simple two step macro - Excel 2013

[bman84]

Hey there, I'm using Excel 2013, and hoping you can help me piece together the code to create a simple macro. I have an Excel spreadsheet that acts as a simple statement generator by populating a number of cells based on a VLOOKUP against a master list of account numbers.

The user enters the 4-digit account number, and the VLOOKUP formulas on a separate tab of the workbook bring back the required values (ie. Customer Name, Address, Balance, etc) from the master list.

Rather than have the user spend time entering an account number, and manually saving a PDF, I'm hoping to create a macro that does those steps for them. Desired process would look like this:

1) User pastes list of account numbers in COLUMN L on 'Entry' sheet
2) User enters a desired file path in cell A5 (location where PDF's will be saved)
3) User clicks a button to start the macro
4) Macro reads first account number in COLUMN L and writes it to cell H7
5) Macro moves to separate sheet called 'Renewal Letter' and saves the sheet as a PDF, with the same filename as the text in cell K6
6) Macro moves back to 'Entry' and writes the next account number from COLUMN L to cell H7
....and the loop of steps 5) and 6) continues until the entire list in COLUMN L has been completed


Thanks in advance for any help!

 

[bman84]

Still getting a Compile Error/Syntax Error for the following line:

oHTMLBody = Replace(oHTMLBody, Chr(13), "

 

[bman84]

Ignore my last post, just found an extra hard return in the code. Fixed. This is awesome! Let me know if you're ever in Toronto and I'll buy you a beer

Still getting a Compile Error/Syntax Error for the following line:

oHTMLBody = Replace(oHTMLBody, Chr(13), "

 

[BiocideJ]

Glad to hear it is working for you.

BTW, the line is SUPPOSED to read

oHTMLBody = Replace(oHTMLBody, Chr(13), "<BR>") 'replace Excel line breaks with HTML line breaks

I forgot the forum converts encoded HTML into actual HTML when you post it so the HTML line break code was actually converted into a line break when I posted.