Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!
  • 0
Sapron75

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

Question

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 :

sub sendPDF()


Dim OutlookApp As Object
Dim OutLookMailItem As Object
Dim PdfFile As String, Title As String
Dim myAttachments As Object

' Not sure for what the Title is
  Title = ActiveSheet.Range("D19")
 
  ' Define PDF filename
  PdfFile = ActiveWorkbook.FullName
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  ' PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf" dit is voor tabbladnaam erbij te geven
  PdfFile = PdfFile & "_concerning_" & ActiveSheet.Range("D19") & ".pdf" ' hiermee wordt de naam van persoon in bestand opgenomen
  ' 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 = "[email protected]" ' <-- 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  !

 

Sapron75

Share this post


Link to post
Share on other sites

0 answers to this question

Recommended Posts

There have been no answers to this question yet

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×