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 !
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,676
Hi, try this modification of the code:
Rich (BB code):
  ' Define PDF filename
  'PdfFile = ActiveWorkbook.FullName
  PdfFile = Environ("Temp") & "\" & ActiveWorkbook.Name
Regards
 

Watch MrExcel Video

Forum statistics

Threads
1,101,817
Messages
5,483,080
Members
407,378
Latest member
swanbrown

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top