# [SOLVED] Word macro to save .docm as pdf with specific filename of surname_&_date



## eyattwop (Nov 2, 2012)

Hi all,

This is my first venture into a forum so I will try to be detailed and concise! Here's the scenario:

Using Word 2007 I have a mail merged .docm file that includes two macros. The first is an AutoOpen macro that opens the "Find Entry" window, prompting the user to search for one recipient. The second macro sends the letter to the recipient via Outlook and prints the letter (to file the old fashioned way!). 

I need to then save the file as a pdf, with the filename of "[Surname] [First Name] [todays date].pdf", which I am currently doing manually. I've tried, without success, to get a macro to input the [Surname] and [First Name] merge fields into the filename in the Save As window. I've also tried bookmarking the fields to reference those in VBA, but I'm now out of my depth!


So there are two things I would love assistance with if possible:

Firstly, solving the auto-filenaming issue to recognise the current entry in the [Surname] and [First Name] merge fields.

Secondly, also having the date in the filename in YY-MM-DD format, to give "[Surname] [First Name] [todays date].pdf" (my letter uses 02 November 2012 format)</SPAN></SPAN>


I'd then like to add this to the end of my macro, which I will include below. Many thanks for taking the time to read this, and even more thanks if you can assist!!


Sub SendAndPrint()
'
' SendAndPrint Macro
' Sends email to student email and prints
'
With ActiveDocument.MailMerge
.Destination = wdSendToEmail
.SuppressBlankLines = True
With .DataSource
.FirstRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
.LastRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
End With
.Execute Pause:=False
End With
With ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
.LastRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
End With
.Execute Pause:=False
End With
End Sub ​


----------



## macropod (Apr 11, 2008)

*Re: Word macro to save .docm as pdf with specific filename of "surname"&"date"*

Cross-posted at: Word macro to save .docm as pdf with specific filename of "surname"&"date"
For cross-posting etiquette, please read: Excelguru Help Site - A message to forum cross posters

You probably need something like:

```
Sub SendAndPrint()
'
' SendAndPrint Macro
' Sends email to student email and prints
'
Dim strPath As String, strSName As String, strFName As String
strPath = ThisDocument.Path
With ThisDocument
  strPath = .Path
  With .MailMerge
    .Destination = wdSendToEmail
    .SuppressBlankLines = True
    With .DataSource
      .FirstRecord = .ActiveRecord
      .LastRecord = .ActiveRecord
      strSName = .DataFields("Surname").Value
      strFName = .DataFields("Firstname").Value
    End With
    .Execute Pause:=False
    .Destination = wdSendToPrinter
    .SuppressBlankLines = True
    .Execute Pause:=False
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    .Execute Pause:=False
    With ActiveDocument
      .SaveAs FileName:=strPath & "\" & strSName & "_" & strFName & "_" & Format(Now, "YY-MM-DD") & ".pdf", _
        Fileformat:=wdFormatPDF
      .Close
    End With
  End With
End With
End Sub
```
PS: When posting code, please use the code tags. They're on the 'Go Advanced' tab.


----------



## eyattwop (Nov 2, 2012)

*Re: Word macro to save .docm as pdf with specific filename of "surname"&"date"*

Hi Paul,

Thanks for your message and apologies for the cross-posting issue! I'm really looking forward to getting into the office on Monday to try this out - will let you know how it goes.

Cheers,

Adam 

(aka Eyattwop)


----------



## eyattwop (Nov 2, 2012)

*Re: Word macro to save .docm as pdf with specific filename of "surname"&"date"*

Hi Paul,

I've had a play and it's really good! Just a couple of things that would make it perfect if possible - the macro opens a new document to save as the pdf. When the macro runs though there is a dialogue box that pops up before the pdf saves - the dialogue is:

"Do you want to save the changes to "Form Letters2"?"

The macro runs to completion if "no" is selected, but there are going to be multiple users for this macro and I don't trust that this will always happen. Is there a way to bypass the dialogue box or automate the selection of "no"?

Also, when the macro creates the pdf it will automatically overwrite an existing file of same name/format/directory. Is there a way to get the old "File already exists - do you wish to replace?" dialogue box to come up in this circumstance?

Many thanks

Adam


----------



## macropod (Apr 11, 2008)

*Re: Word macro to save .docm as pdf with specific filename of "surname"&"date"*

Hi Adam,

Try:

```
Sub SendAndPrint()
'
' SendAndPrint Macro
' Sends email to student email and prints
'
Dim strPath As String, strSName As String, strFName As String, StrNewFile As String
strPath = ThisDocument.Path
With ThisDocument
  strPath = .Path
  With .MailMerge
    .Destination = wdSendToEmail
    .SuppressBlankLines = True
    With .DataSource
      .FirstRecord = .ActiveRecord
      .LastRecord = .ActiveRecord
      strSName = .DataFields("Surname").Value
      strFName = .DataFields("Firstname").Value
    End With
    .Execute Pause:=False
    .Destination = wdSendToPrinter
    .SuppressBlankLines = True
    .Execute Pause:=False
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    .Execute Pause:=False
    StrNewFile = strPath & "\" & strSName & "_" & strFName & "_" & Format(Now, "YY-MM-DD") & ".pdf"
    If Dir(StrNewFile) <> "" Then Kill (StrNewFile)
    With ActiveDocument
      .SaveAs FileName:=StrNewFile, Fileformat:=wdFormatPDF
      .Close SaveChanges:=False
    End With
  End With
End With
End Sub
```


----------



## eyattwop (Nov 2, 2012)

*Re: Word macro to save .docm as pdf with specific filename of "surname"&"date"*

Hi Paul,

Thanks for such a quick reply! The revised code solves the "Do you want to save changes" issue perfectly. Any ideas for the overwrite issue? I'd be happy with ability to overwrite disabled somehow and a dialogue box that says: "file already exists - please save file manually", if this is doable?

Thanks

Adam


----------



## macropod (Apr 11, 2008)

*Re: Word macro to save .docm as pdf with specific filename of "surname"&"date"*

Try changing:
If Dir(StrNewFile) <> "" Then Kill (StrNewFile)
to:
If Dir(StrNewFile) <> "" Then Kill StrNewFile


----------



## eyattwop (Nov 2, 2012)

*Re: Word macro to save .docm as pdf with specific filename of "surname"&"date"*

Hi Paul,

No joy with that I'm afraid - have tried removing the brackets as suggested but the new file is still overwriting the old one. Any ideas?

Many thanks

Adam


----------



## macropod (Apr 11, 2008)

*Re: Word macro to save .docm as pdf with specific filename of "surname"&"date"*

Hi Adam,

Sorry, I thought the idea was to overwrite the existing file. Try:

```
Sub SendAndPrint()
'
' SendAndPrint Macro
' Sends email to student email and prints
'
Dim strPath As String, strSName As String, strFName As String, StrNewFile As String, Rslt
strPath = ThisDocument.Path
With ThisDocument
  strPath = .Path & "\"
  With .MailMerge
    .Destination = wdSendToEmail
    .SuppressBlankLines = True
    With .DataSource
      .FirstRecord = .ActiveRecord
      .LastRecord = .ActiveRecord
      strSName = .DataFields("Surname").Value
      strFName = .DataFields("Firstname").Value
    End With
    .Execute Pause:=False
    .Destination = wdSendToPrinter
    .SuppressBlankLines = True
    .Execute Pause:=False
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    .Execute Pause:=False
    StrNewFile = strSName & "_" & strFName & "_" & Format(Now, "YY-MM-DD")
Retest:
    With ActiveDocument
      If Dir(strPath & StrNewFile & ".pdf") <> "" Then
        Rslt = MsgBox("A file exists with the name:" & vbCr & StrNewFile & vbCr & vbCr & _
              "Please choose an option:" & vbCr & _
              "• Overwrite" & vbTab & vbTab & "(Yes)" & vbCr & _
              "• Save with another name" & vbTab & "(No)" & vbCr & _
              "• Skip (don't save)" & vbTab & vbTab & "(Cancel)", vbYesNoCancel, "Duplicate File Found")
        If Rslt = vbYes Then
          Kill strPath & StrNewFile & ".pdf"
          .SaveAs FileName:=strPath & StrNewFile & ".pdf", Fileformat:=wdFormatPDF, AddToRecentFiles:=False
        ElseIf Rslt = vbNo Then
          StrNewFile = InputBox("Please choose a new filename", "New File Name", StrNewFile)
          GoTo Retest
        End If
      Else
        .SaveAs FileName:=strPath & StrNewFile & ".pdf", Fileformat:=wdFormatPDF, AddToRecentFiles:=False
      End If
      .Close SaveChanges:=False
    End With
  End With
End With
End Sub
```


----------



## eyattwop (Nov 2, 2012)

*Re: Word macro to save .docm as pdf with specific filename of "surname"&"date"*

Hi Paul,

I've had a go with it and I've got to say it is exceptional! This is going to be a paradigm shift in the way our office performs many admin tasks - so on behalf of myself, and my team:

THANK YOU!:grin:

Kind regards

Adam​


----------



## Eyebuddy (Apr 16, 2013)

*Re: [SOLVED] Word macro to save .docm as pdf with specific filename of surname_&_date*

Hi Guys,

I have a need to to create a macro to save word .docx file using the following:

1) The value of the merged field "Surname"
2) The value of the merged field "DateOfBirth" 
3) The string value of a manually entered field "CR" in the document
4) The string value of a manually entered date field in the document.

Eg smith30121995cr16042013.docx

I have tried to modified the macro above but due to my limited knowledge I have been unable to get it working.

Any help will be appreciated.

Regards

Richard


----------



## macropod (Apr 11, 2008)

*Re: [SOLVED] Word macro to save .docm as pdf with specific filename of surname_&_date*

See: Individual merge letters


----------



## macropod (Apr 11, 2008)

*Re: [SOLVED] Word macro to save .docm as pdf with specific filename of surname_&_date*

Cross-posted at: I would like to know how to create a macro to save word .docx file - Microsoft Community
For cross-posting etiquette, please read: Excelguru Help Site - A message to forum cross posters


----------



## Shottie (Jan 21, 2016)

*Re: [SOLVED] Word macro to save .docm as pdf with specific filename of surname_&_date*

Hi Chaps so I'm in a similar predicament. The above code has been plagurised and added into my form, but I need a little extra in mine as I don't want it to save to a default location. I want to allow users to go to a specific folder and nominate which subfolder the pdf saves in.

The way I'm using it is on my template I have a form to fill in the areas that are needed and then on clicking ok it fills in the form and saves it as a pdf code as follows.


```
Private Sub Btn_OK_Click()
    Application.ScreenUpdating = False
    With ActiveDocument
        .Bookmarks("RTitle").Range.Text = RTitle.Value
        .Bookmarks("RName").Range.Text = RName.Value
        .Bookmarks("RSurName").Range.Text = RSurName.Value
        .Bookmarks("RMyRef").Range.Text = RMyRef.Value
        .Bookmarks("RDate").Range.Text = RDate.Value
        .Bookmarks("RAddress1").Range.Text = RAddress1.Value
        .Bookmarks("RAddress2").Range.Text = RAddress2.Value
        .Bookmarks("RAddress3").Range.Text = RAddress3.Value
        .Bookmarks("RPostcode").Range.Text = RPostcode.Value
        .Bookmarks("RName1").Range.Text = RTitle.Value
        .Bookmarks("RSurName1").Range.Text = RSurName.Value
        .Bookmarks("RName2").Range.Text = RName.Value
        .Bookmarks("RSurName2").Range.Text = RSurName.Value
        .Bookmarks("RDOB").Range.Text = RDOB.Value
        .Bookmarks("RBehav").Range.Text = RBehav.Value
        .Bookmarks("RAdvice").Range.Text = RAdvice.Value
        .Bookmarks("YName").Range.Text = YName.Value
        .Bookmarks("YJob").Range.Text = YJob.Value
        .Bookmarks("YCompany").Range.Text = YCompany.Value
        .Bookmarks("YTel").Range.Text = YTelephone.Value
        .Bookmarks("YEmail").Range.Text = YEmail.Value
   End With
    Application.ScreenUpdating = True
Retest:
    With ActiveDocument
        If Dir(RMyRef.Value & ".pdf") <> "" Then
        Rslt = MsgBox("A file exists with the name:" & vbCr & StrNewFile & vbCr & vbCr & _
              "Please choose an option:" & vbCr & _
              "• Overwrite" & vbTab & vbTab & "(Yes)" & vbCr & _
              "• Save with another name" & vbTab & "(No)" & vbCr & _
              "• Skip (don't save)" & vbTab & vbTab & "(Cancel)", vbYesNoCancel, "Duplicate File Found")
        If Rslt = vbYes Then
          Kill RMyRef.Value & ".pdf"
          .SaveAs FileName:=StrPath & RMyRef.Value & ".pdf", Fileformat:=wdFormatPDF, AddToRecentFiles:=False
        ElseIf Rslt = vbNo Then
          StrNewFile = InputBox("Please choose a new filename", "New File Name", StrNewFile)
          GoTo Retest
        End If
      Else
        .SaveAs FileName:=RMyRef.Value & ".pdf", Fileformat:=wdFormatPDF, AddToRecentFiles:=False
      End If
      .Close SaveChanges:=False
    End With

End Sub
```
What can I add into that to make it save to my specific directory and open the saveas window to choose the correct sub folder?

Apologies if this is not the correct area but the rest of the code worked wonderfully so I thought I'd ask here first in the hopes for an answer or signposting to the right place. :smile:


----------



## macropod (Apr 11, 2008)

*Re: [SOLVED] Word macro to save .docm as pdf with specific filename of surname_&_date*

The code you adapted writes the output to a predefined folder. You've changed that part to refer to whatever is specified by RMyRef.Value. I have no idea what that contains. Plus it's not at all obvious as to the circumstances under which you want the Save As dialogue to display. Is it for all saves, or only some?

PS: When posting code, please use the code tags, indicated by the # button on the posting menu.


----------



## Shottie (Jan 21, 2016)

*Re: [SOLVED] Word macro to save .docm as pdf with specific filename of surname_&_date*

Hi Paul,

Sorry for my ineptitude at this. I'm no coder and have very little experience. Perhaps I should go back to start. 

What I'm trying to put to gether it s letter template where users open the template and a form pops up where they can put required information, but not edit the body of the letter. I've managed to get as far coding the form and getting it to fill in the letter template. 

What I'd like to finish off the code with is on clicking the OK button the data from the form goes into the template and then saves the letter as a PDF. I'd like to then give the user the option to save said PDF in a specific folder or subfolder. So the Masterfolder would be "C:\Letters to issue\Customer Letters" - Subfolders in there are by area so e.g. "Exeter".

The areas of the letters differ so there needs to be the option to pick which subfolder it saves to. 

The code I have (without adding in any of what you have above) is:

```
Private Sub Btn_OK_Click()
    Application.ScreenUpdating = False
    With ActiveDocument
        .Bookmarks("RTitle").Range.Text = RTitle.Value
        .Bookmarks("RName").Range.Text = RName.Value
        .Bookmarks("RSurName").Range.Text = RSurName.Value
        .Bookmarks("RMyRef").Range.Text = RMyRef.Value
        .Bookmarks("RDate").Range.Text = RDate.Value
        .Bookmarks("RAddress1").Range.Text = RAddress1.Value
        .Bookmarks("RAddress2").Range.Text = RAddress2.Value
        .Bookmarks("RAddress3").Range.Text = RAddress3.Value
        .Bookmarks("RPostcode").Range.Text = RPostcode.Value
        .Bookmarks("RName1").Range.Text = RTitle.Value
        .Bookmarks("RSurName1").Range.Text = RSurName.Value
        .Bookmarks("RName2").Range.Text = RName.Value
        .Bookmarks("RSurName2").Range.Text = RSurName.Value
        .Bookmarks("RDOB").Range.Text = RDOB.Value
        .Bookmarks("RBehav").Range.Text = RBehav.Value
        .Bookmarks("RAdvice").Range.Text = RAdvice.Value
        .Bookmarks("YName").Range.Text = YName.Value
        .Bookmarks("YJob").Range.Text = YJob.Value
        .Bookmarks("YCompany").Range.Text = YCompany.Value
        .Bookmarks("YTel").Range.Text = YTelephone.Value
        .Bookmarks("YEmail").Range.Text = YEmail.Value
   End With
    Application.ScreenUpdating = True
    Unload Me
    
End Sub
```
I was hoping to use what you had from the other question and alter it to fit my needs.

Sorry to be such a pain.


----------



## macropod (Apr 11, 2008)

*Re: [SOLVED] Word macro to save .docm as pdf with specific filename of surname_&_date*

Try something along the lines of:

```
Private Sub Btn_OK_Click()
Dim bSvd As Boolean, Rslt As Long
With ActiveDocument
  .Bookmarks("RTitle").Range.text = RTitle.Value
  .Bookmarks("RName").Range.text = RName.Value
  .Bookmarks("RSurName").Range.text = RSurName.Value
  .Bookmarks("RMyRef").Range.text = RMyRef.Value
  .Bookmarks("RDate").Range.text = RDate.Value
  .Bookmarks("RAddress1").Range.text = RAddress1.Value
  .Bookmarks("RAddress2").Range.text = RAddress2.Value
  .Bookmarks("RAddress3").Range.text = RAddress3.Value
  .Bookmarks("RPostcode").Range.text = RPostcode.Value
  .Bookmarks("RName1").Range.text = RTitle.Value
  .Bookmarks("RSurName1").Range.text = RSurName.Value
  .Bookmarks("RName2").Range.text = RName.Value
  .Bookmarks("RSurName2").Range.text = RSurName.Value
  .Bookmarks("RDOB").Range.text = RDOB.Value
  .Bookmarks("RBehav").Range.text = RBehav.Value
  .Bookmarks("RAdvice").Range.text = RAdvice.Value
  .Bookmarks("YName").Range.text = YName.Value
  .Bookmarks("YJob").Range.text = YJob.Value
  .Bookmarks("YCompany").Range.text = YCompany.Value
  .Bookmarks("YTel").Range.text = YTelephone.Value
  .Bookmarks("YEmail").Range.text = YEmail.Value
RedoFileSave:
  With Application.Dialogs(wdDialogFileSaveAs)
    .Name = "C:\Letters to issue\Customer Letters\" & RMyRef.Value & ".pdf"
    .Format = wdFormatPDF
    bSvd = .Show
  End With
  If bSvd = True Then
    .Close SaveChanges:=False
  Else
    Rslt = MsgBox("The file has not been saved. Do you wish to exit without saving?", vbYesNo)
    If Rslt = vbYes Then
      .Close SaveChanges:=False
    Else
      GoTo RedoFileSave
    End If
  End If
End With
End Sub
```


----------



## Shottie (Jan 21, 2016)

*Re: [SOLVED] Word macro to save .docm as pdf with specific filename of surname_&_date*

You sir are freaking AWESOME! :dance:

Worked like a charm thank you ever so much!


----------

