Macro to open email

Rob_010101

Board Regular
Joined
Jul 24, 2017
Messages
198
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
VBA Code:
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.
 
Upvote 0
VBA Code:
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:

VBA Code:
Sub emailThisWorkbook()

Kind Regards
 
Upvote 0
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]
1669671899255.png


Before you run the code I have a typo on line 7.
Remove the "Set " from "Set strPDFFullName = ......"
 
Upvote 0
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:

1669672699420.png

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

Thanks for all your help with this
 
Upvote 0
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.
VBA Code:
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.
 
Upvote 0
Solution
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"
 
Upvote 0
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.
 
Upvote 0
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.
Brilliant, that works!!

Thanks very much again for all your help.
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

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