quanziee
07-20-2018, 11:10 PM
Hi, I want to create and display emails with attachments using a web browser. I want a web browser(google chrome, internet explorer etc.) or perhaps even the user's default web browser to pop up when they run the macro. My current code only manages to open the outlook application and display the email. I have been reading up on CDO but I don't quite understand it or how it works.
Here's my current code:
Sub Email_DelayNote()
Dim IsCreated As Boolean
Dim i As Long
Dim PdfFile As String, Title As String
Dim OutlApp As Object
Dim printRange As Range
' Assign Suject of email
Title = Sheets("Delay Note").Range("AF17")
' Define PDF filename
PdfFile = ActiveWorkbook.FullName
i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)
PdfFile = PdfFile & "_" & Sheets("Delay Note").Name & ".pdf"
Set printRange = Range(Sheets("Delay Note").PageSetup.PrintArea) 'only emails print area
' Export chosen sheet as PDF
With Sheets("Delay Note")
printRange.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 = Title
.To = "" ' <-- Put email of the recipient here
.CC = "" ' <-- Put email of 'copy to' recipient here
.Body = "Hi," & vbLf & vbLf _
& "The report is attached in PDF format." & vbLf & vbLf _
& "Regards," & vbLf _
& Application.UserName & vbLf & vbLf 'body of email
.Attachments.Add PdfFile 'attach attachments
' Try to display email
On Error Resume Next
.Display
Application.Visible = True
If Err Then
MsgBox "E-mail was not sent", vbExclamation
Else
End If
On Error GoTo 0
End With
' Delete PDF file
Kill PdfFile
' Release the memory of object variable
Set OutlApp = Nothing
Set OutlookApp = Nothing
End Sub
Here's my current code:
Sub Email_DelayNote()
Dim IsCreated As Boolean
Dim i As Long
Dim PdfFile As String, Title As String
Dim OutlApp As Object
Dim printRange As Range
' Assign Suject of email
Title = Sheets("Delay Note").Range("AF17")
' Define PDF filename
PdfFile = ActiveWorkbook.FullName
i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)
PdfFile = PdfFile & "_" & Sheets("Delay Note").Name & ".pdf"
Set printRange = Range(Sheets("Delay Note").PageSetup.PrintArea) 'only emails print area
' Export chosen sheet as PDF
With Sheets("Delay Note")
printRange.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 = Title
.To = "" ' <-- Put email of the recipient here
.CC = "" ' <-- Put email of 'copy to' recipient here
.Body = "Hi," & vbLf & vbLf _
& "The report is attached in PDF format." & vbLf & vbLf _
& "Regards," & vbLf _
& Application.UserName & vbLf & vbLf 'body of email
.Attachments.Add PdfFile 'attach attachments
' Try to display email
On Error Resume Next
.Display
Application.Visible = True
If Err Then
MsgBox "E-mail was not sent", vbExclamation
Else
End If
On Error GoTo 0
End With
' Delete PDF file
Kill PdfFile
' Release the memory of object variable
Set OutlApp = Nothing
Set OutlookApp = Nothing
End Sub