mail merge in excel?

[jeo1]

Im wondering how you can mail merge in excel (not MS word) -

when I say mail merge I mean to automatically insert a batch of addresses into a batch of excel documents [one address goes into each of the excel invoices] -

Ive found guides on how to do this if your documents/invoices are created in word but no guide on how to mail marge into an excel invoice document –

is there any guide on how to do this?

[Pepe Le Mokko]

What's wrong with using MsWord's Mail Merge ?
Lots of info at https://www.msofficeforums.com/mail-merge/

[macropod]

Im wondering how you can mail merge in excel (not MS word)

You can't. You can only do mailmerges using applications that include that functionality (e.g. Word, Publisher). Anything else requires the user to develop their own system (in Excel, usually employing VBA and an output worksheet)

[jeo1]

What's wrong with using MsWord's Mail Merge ?
Lots of info at https://www.msofficeforums.com/mail-merge/

because I'm using excel to create invoices, it's easier for me to make invoices in excel than word

but if macropod is correct in saying there's no built in functionality to do a mail merge in an excel document itself, then I'll have to switch to using word to create invoices annoyingly

[jeo1]

I wonder why microsoft have lots of excel invoice templates here if there's no way to do a mail merge in excel, i'd be thinking that'd be a definite feature available if they went through the effort of making invoice templates

[torachan]

If you are already producing your invoices in Excel it is only one more small step to save & print from pdf's.
Upload a sample workbook as per the big yellow banner at the head of the page.
All that is needed is a small VBA app and you keep everything in one file.
torachan.

[maniacb]

@torachan is correct, just load your working file as per the yellow banner and we can assist further.

[macropod]

I wonder why microsoft have lots of excel invoice templates here if there's no way to do a mail merge in excel, i'd be thinking that'd be a definite feature available if they went through the effort of making invoice templates

Because generating invoices from a template and doing a mailmerge are not the same thing. You would still have to write formulae and/or VBA code to populate the templates and save the output. A major difference is that, whereas out of the box a mailmerge produces a single new document containing completed invoices for all the processed records, a formula-driven Excel template cannot and VBA-driven ones rarely do.

[jeo1]

Here's a sample workbook, I'm trying to setup a thing where the "to" address will be filled in with a list of addresses I have in another excel file

[jeo1]

And here's a sample address book i'd be inserting into each file (in this sample I've only one address entered, but in my own theres about 60 addresses in the excel file

I'm trying to automatically insert each of these addresses into the sample workbook file, making it so that there's be 60 of these workbooks each one with a particular address written into it

can you tell me how i'd go about doing this for excel invoice files, using the small VBA app?

[maniacb]

For simplicity sake and efficiency, can the second workbook be the second sheet in the invoice workbook? Do you have to have two workbooks? Also, you list five lines in the address spreadsheet, but the invoice has both billing, and shipping address lines, duplicate fields, which do you want populated. And thirdly, will you also be populating the invoice number and the customer id?

Once you 'mail merge', do you want to send an email or just create a PDF, or both? If using Email, do you use Outlook?

[Pepe Le Mokko]

Because generating invoices from a template and doing a mailmerge are not the same thing. You would still have to write formulae and/or VBA code to populate the templates and save the output. A major difference is that, whereas out of the box a mailmerge produces a single new document containing completed invoices for all the processed records, a formula-driven Excel template cannot and VBA-driven ones rarely do.

Well put

[jeo1]

For simplicity sake and efficiency, can the second workbook be the second sheet in the invoice workbook? Do you have to have two workbooks?

It can be the 2nd sheet if that makes things easier yes, I don't need two workbooks - just one sheet for the invoice and a 2nd sheet for the addresses to be entered into the invoice

Also, you list five lines in the address spreadsheet, but the invoice has both billing, and shipping address lines, duplicate fields, which do you want populated.

I'll be removing the "ship to" filed and just using the "to" field, so you can ignore the "ship to" field, I'll just be filling in the "to" field

And thirdly, will you also be populating the invoice number and the customer id?

yes I will

Once you 'mail merge', do you want to send an email or just create a PDF, or both? If using Email, do you use Outlook?

I'll just be creating a PDF, no need to email it

[maniacb]

Are you expecting to also populate the Item/Description/... line with this process? Are all invoices based on a single line or multiple lines of these? Or is the information in this line static(not customer specific) for all customer invoices?

[jeo1]

is the information in this line static(not customer specific) for all customer invoices?

for now it's static but in the future I may be changing it

[maniacb]

Try out this process

Please Login or Register  to view this content.

[jeo1]

Many thanks pal that worked, is it possible to customize it so that the text you enter can be a different size and type of font?

[maniacb]

All you have to do is change the format on the invoice sheet.

[jeo1]

Ah yes I see that now

would anyone know how to make cell H23 display the figure amount of how much the tax is? E.g. In my area the tax is 20%, so if the subtotal was 5£ that'd be a tax of £1

so instead of it displaying H23 as "0.2" I want it displayed as £1 https://i.imgur.com/ycGRXXU.png

[maniacb]

Use the same format function and select the option for percent

[jeo1]

Cheers buddy, I've another question if you get a chance

for the invoice workbook I'm using strangely any text that I post under row 27 doesn't appear when I press ctrl+P

I had a look through the menu tabs here https://i.imgur.com/QaonHAn.png but couldn't find any differences so I'm not sure why the text [below row 27] doesn't appear in the print screen

also doesn't appear when I save the excel file as a pdf

[maniacb]

If you go to 'view->Page Break Layout' you can see that only certain ranges are visible. You can drag down to make the area greater.

[yurttas]

Here is sample mail merge in excel but in Turkish.

[jeo1]

Try out this process

Please Login or Register  to view this content.

for this VBA script, would it be possible to make the PDF files automatically go into another folder? E.g. I want to make the PDF files go into a subfolder of where the excel workbook is, and this folder is called "PDFs"

[maniacb]

Update this line

Please Login or Register  to view this content.

[jeo1]

Would there be a line of code that makes it so the macro enters in a random 8 digit code into a specific cell each time? So that each PDF document that's created would have it's own randomly generated 8 digit code

E.g. If I wanted to write a randomly generated 8 digit code into cell H8 what "value =" code would you use for this?