Since O365 -> macro pdf to e-mail is failing
Results 1 to 2 of 2

Thread: Since O365 -> macro pdf to e-mail is failing
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2016
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Since O365 -> macro pdf to e-mail is failing

    Hi, Recently I have Office 365 and also SharePoint with the new "Team Sites" environment (previously called workgroups). Anyhow, I have a macro which convert the excel sheet into pdf and puts this pdf in an email ready to send.

    This is the coding :

    Code:
    sub sendPDF()
    
    Dim OutlookApp As Object
    Dim OutLookMailItem As Object
    Dim PdfFile As String, Title As String
    Dim myAttachments As Object
    
    Title = ActiveSheet.Range("D19")   
    
    ' Define PDF filename  
    PdfFile = ActiveWorkbook.FullName
    i = InStrRev(PdfFile, ".")  
    If i > 1 Then PdfFile = Left(PdfFile, i - 1)  
    PdfFile = PdfFile & "_concerning_" & ActiveSheet.Range("D19") & ".pdf" 
    
    ' Export activesheet as PDF  
    With ActiveSheet    
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False  
    End With
    
    ' Use already open Outlook if possible  
    On Error Resume Next  
    Set OutlApp = GetObject(, "Outlook.Application")  
    If Err Then    
    Set OutlApp = CreateObject("Outlook.Application")    
    IsCreated = True  
    End If  
    OutlApp.Visible = True  
    On Error GoTo 0   
    
    ' Prepare e-mail with PDF attachment  
    With OutlApp.CreateItem(0)       
    
    ' Prepare e-mail    
    .Subject = "MPR " & ActiveSheet.Range("H16")    
    .To = "example@example.com" ' <-- Put email of the recipient here    
    .Body = "Dear, "    
    .Attachments.Add PdfFile    
    .Display    
    End With
    
    End Sub
    The excel file is located in a sharepoint team site, as it was previously in a workgroup. When running this macro, the e-mail is being prepared but when the pdf must be attached, Outlook will show the pdf as attachment, but underneath the pdfname there is notification of "Download failed" . Then I have to doubleclick it, then a notification comes up with the text, " you don't have the correct authorization" with two options "Again" and "Delete" . If I click again, then the pdf will be attached... . How can I fix this ?

    Thank you !

  2. #2
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,524
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Since O365 -> macro pdf to e-mail is failing

    Hi, try this modification of the code:
    Code:
      ' Define PDF filename
      'PdfFile = ActiveWorkbook.FullName
      PdfFile = Environ("Temp") & "\" & ActiveWorkbook.Name
    Regards
    Vladimir Zakharov

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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