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

Sapron75

New Member
Joined
Jun 6, 2016
Messages
15
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 !
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Hi, try this modification of the code:
Rich (BB code):
  ' Define PDF filename
  'PdfFile = ActiveWorkbook.FullName
  PdfFile = Environ("Temp") & "\" & ActiveWorkbook.Name
Regards
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top