Command Button to send email

ReignEternal

New Member
Joined
Apr 11, 2021
Messages
30
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: 5

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,197
Office Version
  1. 2007
Platform
  1. Windows
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
 
Solution

ReignEternal

New Member
Joined
Apr 11, 2021
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,197
Office Version
  1. 2007
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,394
Messages
5,769,828
Members
425,574
Latest member
grimeslisa

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
Top