# macro to separate mail merge document and password protect



## flyers2thecup (Dec 13, 2012)

Hello Everyone! 

My first time here...and in a pinch. I bet you never heard that before, right? 

Anyways, I'm trying to assist someone here at work save some time. They are generating a word document from mail merge. They are trying to save off each page, password protect it and email them. 

I've never worked with VBA in word...only a little in excel. And I don't pretend to know much. 

I was hoping at the very least I would be able to find out how to run a macro that saves off each letter into a specified folder and password protect it. 

Can someone assist with that?

Thank you!!!
Angelo


----------



## macropod (Apr 11, 2008)

If the idea is to email the documents, why not merge to email? Besides, if you password-protect the documents, how are the recipients to open them (surely you're not going to email the password as well!!)?

For the splitting, see: Individual merge letters. There you'll find a complete add-in or, if you prefer, a macro to do just the splitting.


----------



## flyers2thecup (Dec 13, 2012)

The passwords would be sent to the user under separate cover.


----------



## flyers2thecup (Dec 13, 2012)

it also appears that the numbers i have in the mail merge are getting a bunch of digits added after the decimal. I made very sure that the data i have has nothing behyond the 2 decimal places....so not sure why it's doing that. 

This macro seems to work partially at a time. Either the numbers are wrong...or some sheets end up blank...just can't get it to run and get all files will correct numbers. very frustrating.


----------



## macropod (Apr 11, 2008)

To control number & currency formatting in Word, add a numeric picture switch to the mergefield. To do this:
• select the field;
• press Shift-F9 to reveal the field coding. It should look something like {MERGEFIELD MyData};
• edit the field so that you get {MERGEFIELD MyData \# $,0.00} (or whatever other numeric format you prefer - see below);
• position the cursor anywhere in this field and press F9 to update it;
• run your mailmerge.
Note1: The '\# $,0.00' in the field is referred to as a numeric picture switch. Other possibilities include:
• \# 0 for rounded whole numbers
• \# ,0 for rounded whole numbers with a thousands separator
• \# ,0.00 for numbers accurate to two decimal places, with a thousands separator
• \# $,0 for rounded whole dollars with a thousands separator
• \# "$,0.00;($,0.00);'-'" for currency, with brackets around negative numbers and a hyphen for 0 values
The precision of the displayed value is controlled by the '0.00'. You can use anything from '0' to '0.000000000000000'.
If you use a final ';' in the formatting switch with nothing following, (eg \# "$,0.00;($,0.00);") zero values will be suppressed. Note that this suppresses 0s resulting from empty fields and from fields containing 0s.


----------



## flyers2thecup (Dec 13, 2012)

Thank you VERY MUCH!!! that worked perfectly. Now...is there an easy way to password protect each document in a folder? same password for each will do. 

Once that's complete...can a macro be used to send each document in an email?

Found something else to...the split mail merge add-in changes all the formatting. Can a macro fix the formatting of all the documents in a folder?


----------



## flyers2thecup (Dec 13, 2012)

when i run the mailmerge split...ever other file it creates is blank. Any idea on what is causing that?

thanks!!


----------



## macropod (Apr 11, 2008)

Password-protecting a file and sending it to someone by email and having to send the password by email as well - even if it's a separate email - is just plain stupid. Anyone who can access any of the servers through which the emails pass will be able to quite easily access the document contents. Emails give you as much privacy as a postcard and should be treated the same. Would you send someone your bank account PIN/password on a postcard? Somehow I doubt it.

As the mailmerge addin isn't mine, you should direct any questions about its performance to the website's owner. Likewise for the macro. As the splitter macro in the link isn't one I've written (and therefore I'm not familiar with all its workings), I'm reluctant to start messing around with it. That said, if you're getting empty files, that suggests your mailmerge main document might have a Section break that's messing things up.

And yes, a macro could be used to passwword-protect the files and send emails. Ideally, any such functionality would be added to the existing code, rather than developing an entirely separate procedure.


----------



## flyers2thecup (Dec 13, 2012)

I've resorted to just running the mail merge by itself...and saving off each document to a folder. 

do you happen to have a macro to password protect a group of files in folder?

Thanks!!


----------



## macropod (Apr 11, 2008)

You could use a macro like:

```
Sub ProtectDocuments()
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String, wdDoc As Document
strFolder = GetFolder
If strFolder = "" Then Exit Sub
strFile = Dir(strFolder & "\*.doc", vbNormal)
While strFile <> ""
  Set wdDoc = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
  With wdDoc
    .Password = "Some password"
    .SetPasswordEncryptionOptions PasswordEncryptionProvider:= _
      "Microsoft RSA SChannel Cryptographic Provider", PasswordEncryptionAlgorithm:="RC4", _
      PasswordEncryptionKeyLength:=56, PasswordEncryptionFileProperties:=True
    .Close SaveChanges:=True
  End With
  strFile = Dir()
Wend
Set wdDoc = Nothing
Application.ScreenUpdating = True
End Sub

Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function
```
As I don't know the folder details, I've included a folder browser. As coded, the macro applies the same password "Some password" (change to suit your needs) to all Word documents in the folder.


----------



## flyers2thecup (Dec 13, 2012)

THANK YOU VERY MUCH!!! 

Now when I have my files ready to go, would a macro be used to email the documents to each person? 

Again, thank you for your time! It's greatly appreciated.


----------



## macropod (Apr 11, 2008)

As I already said, a macro could be used for that. However, as I'm not overly familiar with email application programming and I know nothing of your email app, where the email addresses are stored, how the documents & email addresses can be matched or what other content you might want to include in the emails, I'm not the one to do that coding.


----------



## flyers2thecup (Dec 13, 2012)

Ok...i got the email part figured out. But i came across a hiccup....which i should have known was going to happen. I manually separated all the memos from the mail merge. Now the powers that be want to change a portion of the memo.


----------

