Macro to open email

[Rob_010101]

Hello,

I've created a form in excel and added a button at the bottom.

I'd like to assign this button a macro that when it's pushed it:

1. Attaches the Excel as a PDF to a new email (the PDF can be named Maternity Leave Notification Form)
2. Looks at Cell E33 for the email address to send to and adds this into the "To" field of the email
3. Sets the subject line as "Maternity Leave Dates Notification Form"
4. Adds specific text into the body of the email.

I can specify this text if needed or can be told where to add it into the code myself.

Many thanks in advance,
Chris

 

[MCLIFTO8]

Sub emailThisWorkbook()
    Dim appOutlook As Outlook.Application
    Dim mEmail As Outlook.MailItem

    Set appOutlook = New Outlook.Application
    Set mEmail = appOutlook.CreateItem(olMailItem)
    Set strPDFFullName = ThisWorkbook.Path & "\Maternity Leave Notification Form.pdf"

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPDFFullName

    With mEmail
         .To = ThisWorkbook.Sheets("Sheet1").Range("E33").Value2
         .CC = ""
         .BCC = ""
         .Subject = "Maternity Leave Dates Notification Form"
         .HTMLBody = "specific text"
         .Attachments.Add strPDFFullName
         .Display
    End With
    Set mEmail = Nothing
    Set appOutlook = Nothing
End Sub

This is untested, it may require a little tweaking.

 

[Rob_010101]

Sub emailThisWorkbook()
    Dim appOutlook As Outlook.Application
    Dim mEmail As Outlook.MailItem

    Set appOutlook = New Outlook.Application
    Set mEmail = appOutlook.CreateItem(olMailItem)
    Set strPDFFullName = ThisWorkbook.Path & "\Maternity Leave Notification Form.pdf"

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPDFFullName

    With mEmail
         .To = ThisWorkbook.Sheets("Sheet1").Range("E33").Value2
         .CC = ""
         .BCC = ""
         .Subject = "Maternity Leave Dates Notification Form"
         .HTMLBody = "specific text"
         .Attachments.Add strPDFFullName
         .Display
    End With
    Set mEmail = Nothing
    Set appOutlook = Nothing
End Sub

This is untested, it may require a little tweaking.

Hello

I got

Compile Error:

User-defined type not defined

On this line:

Sub emailThisWorkbook()

Kind Regards

 

[MCLIFTO8]

That error is because you're missing the reference to the object.

In the VBA editor:
Stop debugging.
Tools>Refererences>[Microsoft Outlook xx.x Object library]

Before you run the code I have a typo on line 7.
Remove the "Set " from "Set strPDFFullName = ......"

 

[Rob_010101]

That error is because you're missing the reference to the object.

In the VBA editor:
Stop debugging.
Tools>Refererences>[Microsoft Outlook xx.x Object library]
View attachment 79757

Before you run the code I have a typo on line 7.
Remove the "Set " from "Set strPDFFullName = ......"

Hi Again

Would everyone who uses the form have to add that reference? It'll be going on an intranet page and will be used by management.

I have got it to work but the PDF comes out like this:

It's not the biggest issue but wonder if it could be changed?

Thanks for all your help with this

 

[MCLIFTO8]

Hi Again

Would everyone who uses the form have to add that reference? It'll be going on an intranet page and will be used by management.

I have got it to work but the PDF comes out like this:

View attachment 79759
It's not the biggest issue but wonder if it could be changed?

Thanks for all your help with this

The Reference is attached to the project so it shouldn't need reinitialising.

IF you have problems, you can try late binding. It's not recommended but removes the need to reference the object library.

Sub emailThisWorkbook()
    Dim appOutlook As Object
    Dim mEmail As Object

    Set appOutlook = CreateObject("Outlook.Application")
    Set mEmail = appOutlook.CreateItem(olMailItem)
    strPDFFullName = ThisWorkbook.Path & "\Maternity_Leave_Notification_Form.pdf"

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strPDFFullName

    With mEmail
         .To = ThisWorkbook.Sheets("Sheet1").Range("E33").Value2
         .CC = ""
         .BCC = ""
         .Subject = "Maternity Leave Dates Notification Form"
         .HTMLBody = "specific text"
         .Attachments.Add strPDFFullName
         .Display
    End With
    Set mEmail = Nothing
    Set appOutlook = Nothing
End Sub

Regarding the name of the maternity leave application form....
I don't have an immediate answer. Replace the spaces in the names code with underscores and it will at least be readable.

 

[Rob_010101]

Also, if I may

How would you enable multiple lines for the text in the email body?

So it could look like:

"Hello,

Please find attached my Maternity Leave notification form which contains the dates I'd like to begin and end my leave.

Any holiday dates mentioned have been requested through Dayforce for your approval, if not already approved.

Please forward this to the HR Business Partner supporting our Location/Department.

Many Thanks"

 

[MCLIFTO8]

Also, if I may

How would you enable multiple lines for the text in the email body?

The email is written in HTML.
If you just want some simple new lines try appending vbNewLine to your string.
eg "This is line one." & vbNewLine & "This is line two." & vbNewLine & vbNewLine & "This is line four."

If you want full control over your formatting, use an online HTML editor, view the source and paste that into your excel HTML body.

Free online HTML editor - onlinehtmleditor.dev

Free online HTML editor with feature-rich WYSIWYG Editor powered by CKEditor. Get the source code of the content immediately.

onlinehtmleditor.dev

 

[Rob_010101]

The email is written in HTML.
If you just want some simple new lines try appending vbNewLine to your string.
eg "This is line one." & vbNewLine & "This is line two." & vbNewLine & vbNewLine & "This is line four."

If you want full control over your formatting, use an online HTML editor, view the source and paste that into your excel HTML body.

Free online HTML editor - onlinehtmleditor.dev

Free online HTML editor with feature-rich WYSIWYG Editor powered by CKEditor. Get the source code of the content immediately.

onlinehtmleditor.dev

Brilliant, that works!!

Thanks very much again for all your help.