Hi Vladimir!
Thank you for your reply this late.
I tried to do as you said, but initially it gave the same effect, unfortunately. The original Excel file is still attached (and is in a share point location and therefore wont show, but I still need it in pdf for obvious reason). I also got a bit confused if you ment "
IgnorePrintAreas:=Try" or as you placed in the code "=False".
Another thing that I started wondering about, does the other two buttons have som sort of wierd relations to each other? They're all sepereated by the line and the "Private Sub Commend" / "End Sub" and I would assume they would not have any connection. Either way, Vladimir. I really appiriciate your help, I really do!
Here are the errors I got after the "normal ones" to begin with:
- Run Time error '53' and when debugged, it goes to the Kill PDF and highlights it. This is the same error I got before I posted and even though bot the "To" and "Bcc:" recipent recieves email, the PDF is not attached.
- Then I did my best to copy your code directly to mine and then I got "Runtime Error 2147024894 (80070002) - Cannot locate file. Check if file path and file name is correct".
Here is the code with your edits:
Private Sub CommandButton2_Click()
Dim IsCreated As Boolean
Dim i As Long
Dim PdfFile As String, Title As String
Dim OutlApp As Object
' Not sure for what the Title is
Title = Range("A1")
' Define PDF filename
PdfFile = ActiveWorkbook.FullName
i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)
PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"
' Export activesheet as PDF
With ActiveSheet
.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Users\Ron Holan\Desktop" & ActiveSheet.Range("C9").Value & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
From:=1, To:=1, _
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 = "FRS Budsjettilbud fra Bioteknikk AS"
.To = " " & ActiveSheet.Range("F10").Value & " " '
.CC = "" ' <-- Put email of 'copy to' recipient here
.Bcc = "ron@bioretur.no" ' <-- Put email of ' blind copy to' recipient here
.Body = "Hi, " & ActiveSheet.Range("F9").Value & ", " & vbLf & vbLf _
& " " & vbLf _
& "Attached you will find our budgett offer regarding the FRS as discussed. If you have any questions, please do not hesitate to contact us. We are looking forward to cooperate with you." & vbLf & vbLf _
& "Best regards," & vbLf _
& " " & ActiveSheet.Range("D46").Value & " " & vbLf & vbLf
.Attachments.Add PdfFile
' Try to send
On Error Resume Next
.Send
Application.Visible = True
If Err Then
MsgBox "E-mail was not sent", vbExclamation
Else
MsgBox "E-mail successfully sent", vbInformation
End If
On Error GoTo 0
End With
' Delete PDF file
Kill PdfFile
' Quit Outlook if it was created by this code
If IsCreated Then OutlApp.Quit
' Release the memory of object variable
Set OutlApp = Nothing
End Sub
