Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Dec 2016
    Posts
    120
    Thanks
    51
    Thanked 1 Time in 1 Post

    I can't change the PDF name using VBA

    Hello,

    I'm trying to send a sheet in excel as pdf in outlook. I would love to know how i can change the pdf attached name that gets sent in outlook from excel using vba.

    This is part of the code:


    Code:
    strPath = Environ$("temp") & "\"
      
        strFName = Replace(ActiveWorkbook.Name, ".pdf", "")
        strFName = Left(strFName, InStrRev(strFName, ".") - 1) & " _ " & ws3.Range("E10").Value _
        & " _ " & Format(Now, "dd-mmm-yy") & ".pdf"
       
        ws3.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
            strPath & strFName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    i tried to change ActiveWorkbook.Name but it gives me an error. Can someone please help?

    Thank you.

  2. #2
    Super Moderator macropod's Avatar
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,177
    Thanks
    2
    Thanked 455 Times in 374 Posts
    Without knowing what the error message says, it's hard to say. Are you sure ws3.Range("E10").Value doesn't contain any illegal filename characters?

    Also, I can't see the point of:
    strFName = Replace(ActiveWorkbook.Name, ".pdf", "")
    Your activeworkbook is unlikely to have a .pdf extension.

    It seems to me you could get the same result by replacing:
    strFName = Replace(ActiveWorkbook.Name, ".pdf", "")
    strFName = Left(strFName, InStrRev(strFName, ".") - 1) & " _ " & ws3.Range("E10").Value & " _ " & Format(Now, "dd-mmm-yy") & ".pdf
    with:
    strFName = Split(ActiveWorkbook.Name, ".xls")(0) & " _ " & ws3.Range("E10").Value & " _ " & Format(Now, "dd-mmm-yy") & ".pdf"
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •