Command Button to send email

ReignEternal

New Member
Joined
Apr 11, 2021
Messages
41
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have looked through a few of the other threads for this but I have a command button that i want to display an email once the button is clicked. I kep getting an error that says variable not defined. Also in the second image, the two regions of vba keep getting highighted. Overall i am looking to have someone click the "Send Email" button and have it the display hte email and attach the source file path

1636412041339.png


1636412618846.png


VBA Code:
Private Sub cmdEmail_Click()

    On Error GoTo ErrHandler
    
'     SET Outlook APPLICATION OBJECT.
    Dim olApp As Object
    Set olApp = CreateObject("Outlook.Application")
    
'     CREATE EMAIL OBJECT.
    Dim olEmail As Object
    Set olEmail = olApp.CreateItem(olMailItem)

    With olEmail
        .to = "emailaddresswilllivehere"
        .Subject = "This is the Subject of the E-mail"
        .Body = "This is the Body of the E-mail"
        .Display        ' Display the message in Outlook.
        .Attachments.Add Source
        Source = ThisWorkbook.FullName
    End With
    
'    CLEAR.
    Set olEmail = Nothing:    Set olApp = Nothing
        
ErrHandler:
    '
End Sub
 

Attachments

  • 1636412149963.png
    1636412149963.png
    26.9 KB · Views: 27

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Try this:

VBA Code:
Private Sub cmdEmail_Click()
'     SET Outlook APPLICATION OBJECT.
    Dim olApp As Object
    Set olApp = CreateObject("Outlook.Application")
    
'     CREATE EMAIL OBJECT.
    Dim olEmail As Object
    Set olEmail = olApp.CreateItem(0)

    With olEmail
        .to = "emailaddresswilllivehere"
        .Subject = "This is the Subject of the E-mail"
        .Body = "This is the Body of the E-mail"
        .Display        ' Display the message in Outlook.
        .Attachments.Add ThisWorkbook.FullName
    End With
    
'    CLEAR.
    Set olEmail = Nothing:    Set olApp = Nothing
End Sub
 
Upvote 0
Solution
Try this:

VBA Code:
Private Sub cmdEmail_Click()
'     SET Outlook APPLICATION OBJECT.
    Dim olApp As Object
    Set olApp = CreateObject("Outlook.Application")
   
'     CREATE EMAIL OBJECT.
    Dim olEmail As Object
    Set olEmail = olApp.CreateItem(0)

    With olEmail
        .to = "emailaddresswilllivehere"
        .Subject = "This is the Subject of the E-mail"
        .Body = "This is the Body of the E-mail"
        .Display        ' Display the message in Outlook.
        .Attachments.Add ThisWorkbook.FullName
    End With
   
'    CLEAR.
    Set olEmail = Nothing:    Set olApp = Nothing
End Sub
Thank you for this. I see that you removed some text from the vba after "Set olEmail = olApp.CreateItem(olMailItem)". You swapped olMailItem with 0. Can you clue me in as to why tis was the error?
 
Upvote 0
Apparently some version of excel does not recognize the description of the OlItemType, so I use the numeric value.


Maybe someone else can tell us about the bug of this object.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

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