How to email word document as an attachment with filename taken from text in document

Hi,

I have a word document to which few cells are linked from an excel sheet. what I am trying to do is send this word document in email as pdf attachment. The challenge is I want the file name to be the text that is being pulled from excel sheet. Following is the code I am using to email the document.

Sub Saveandemail()

Dim OL              As Object
Dim EmailItem       As Object
Dim Doc             As Document
Dim strFileName     As String
 
 Application.ScreenUpdating = False
    Set OL = CreateObject("Outlook.Application")
    Set EmailItem = OL.CreateItem(olMailItem)
    Set Doc = ActiveDocument

    strFileName = Replace(Doc.FullName, ".docm", ".pdf")
    Doc.ExportAsFixedFormat OutputFileName:=strFileName, _
        ExportFormat:=wdExportFormatPDF
 
    With EmailItem
        .Subject = ""
        .Body = ""
        .To = ""
        .Attachments.Add strFileName
        .Send
    End With
 
    Application.ScreenUpdating = True

    Set Doc = Nothing
    Set EmailItem = Nothing
    Set OL = Nothing
    
 End Sub

Can you please help me how to get the filename to be the cell value that is linked to excel sheet.

That really depends on 'how' the cell exists in the document. For example, if it's
• contained in an embedded Excel worksheet, you'll have to open the embedded object and extract it from there.
• just a text string retrieved via a LINK field, you could simply bookmark that field an retrieve the bookmark's contents.
You need to provide more details.

In that case, if you bookmark with LINK field with the name 'Filename', for example, you could replace:
strFileName = Replace(Doc.FullName, ".docm", ".pdf")
Doc.ExportAsFixedFormat OutputFileName:=strFileName, _
ExportFormat:=wdExportFormatPDF
with:

With Doc
  strFileName = Replace(.FullName, .Name, .Bookmarks("Filename").Range.Text & ".pdf")
  .SaveAs2 FileName:=strFileName, FileFormat:=wdFormatPDF, AddToRecentFiles:=False
  .Close False
End With

Thank You! very much. That worked after a small change to the code, as document was closing when macro was run.

With Doc
  strFileName = Replace(.FullName, .Name, .Bookmarks("Filename").Range.Text & ".pdf")
  .SaveAs2 FileName:=strFileName, FileFormat:=wdFormatPDF, AddToRecentFiles:=False
End with

I'm sorry but still there is a problem. Every time the macro is run, copy of the pdf file is getting saved in the folder. The pdf file is to be only an attachment but not in folder

You can attach a file that hasn't been saved. You could add a line to the code to delete the pdf after the email has been sent. For example:

With EmailItem
        .Subject = ""
        .Body = ""
        .To = ""
        .Attachments.Add strFileName
        .Send
End With
Kill strFileName

The only way is to study VBA coding - for which you can learn from the coding of others - and use it. The last part is particularly important: use it or lose it!