How to trigger Outlook Email from excel with the click of a button?

Insert a button and assign a macro to it.

Dim OutApp As Object
Dim OutMail As Object
                
                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)
                
                On Error Resume Next
                
                With OutMail
                    .To = ""
                    .CC = ""
'UNDO THE ' ON THE LINE BELOW TO ADD ACTIVE WORKBOOK
                    '.Attachments.Add ActiveWorkbook.FullName
                End With    
                            
                'BODY OF THE EMAIL GOES HERE. IF YOU NEED A SPACE OR NEW LINE YOU HAVE TO PUT THE "" & vbNewLine &_ in as seen below.
                strbody = "Your message is ready to be sent with the following file or link attachments:" & vbNewLine & vbNewLine & _
                        "" & vbNewLine & _
                        "" & vbNewLine & _
                        "" & vbNewLine & _
                        ""
                            
                With OutMail
                    .Subject = ""
                    .Body = strbody
'UNDO THE ' BELOW IF YOU WANT TO SEE IT BEFORE YOU SEND IT BUT THEN PUT A ' ON THE .SEND TO STOP IT FROM SENDING AUTOMATICALLY
                    '.Display
                    .send
                End With
                                         
                On Error GoTo 0
                
                Set OutMail = Nothing
                Set OutApp = Nothing

Here are step by step instructions on how to put the macro in place to work for you.

1. Hold ALT and press F11 to open VBA editor
2. Then at the top click Insert then module
3. Then copy and paste the code below (when we achieve your custom code)

The headers will need to be in the place where they are going to stay in the worksheet so that the macro knows where to pull which data piece from. You will need to make an email column and put an email for each row.

You mentioned you have a word file that you want to be the body. I will either need the location of it or for me it would be easier if you copied and pasted it into a cell in your excel spreadsheet then I could just reference that value as the body. If something in the body of that letter changes based on the row values then you either you or I can set that up in excel to replace those spots based on the values in the row we are on.

Oh and do you want to see each email before you send it or not?

This is make the automated process a slightly more manual one. Keep in mind if you have the same process being done over and over again its better to leave it to being automatic than to see each email before it goes out. If you want to test it you can always only change one row to yes and modify the email temporarily to your personal email for testing.

Hi jessebranum777,

Yes I do want to see the mail before I send it and i do agree that it will be slightly manual.But this is required as there will be more data in that mail body which varies all the time and is not included in the excel so cannot be referenced from the excel.This data will be added manually after the email is composed.

The mail body format that I need is exactly as seen in the attached word file;all fields in that including questions will remain the same for every mail.And the corresponding fields in the mail body should be filled by referencing corresponding cells in that particular row as soon as the dropdown for that row is switched to "Yes".
I have shown this in the attached "Autofilling" file.Yellow highlighted part in the 1st line need to be filled as per the arrows shown & Yellow highlighted parts in the table needs to have data in front of them from the corresponding cells in excel.

With your steps I got how to and where to put this code.Thanks a lot for that.However I don't understand how do I incorporate this mail body in the VB code and how the cells from that excel can be referenced in the mail body and filled automatically when the mail is composed.

Request your assistance again here.
Thanks

I have an idea that is much easier however more manual than pressing one button like a macro. And since you want to see the email each time and have a specific format in word that you want to use for the email. What I would do is a mail merge then send the emails.

https://support.office.com/en-us/art...b-0a948fa3d7d3

If this was able to solve your issue please click * and give rep to those whom have helped you and mark the thread as solved. Thanks.

Hi jessebranum777,

Thanks for the option.However this process as you mentioned is bit more manual.eg.xlsmWord KE.docxFinal.xlsmThis may not help me really.I needed this to be coded into a macro only.So that everytime writup of the mail and filling all data in it can be avoided as soon as user switches dropdown option to "Yes" for that particular row.

I would be really thankful if you could please help me with the macro..

Okay but keep in mind if you want to look at each email before they go out and you have 100+ emails you will have 100+ emails generated and stay open until you either send them or the macro crashes or the computer due to the large amount of emails open. This macro is not finished as you are missing a spot for the email address, subject, body, you have to save your document in a certain directory then copy that directory onto the macro since I don't know its location. This however will not put the body of the email as the word document you presented but will attached the word document you presented with the appropriate information you mentioned where is should go. Let me know if we have begun down the right track here.

Sub ifthensend()

Dim wApp As Word.Application
Dim wDoc As Word.Document
Dim DtRNG As Range, Dt As Range
Dim OutApp As Object
Dim OutMail As Object
Set DtRNG = Sheets("Sheet1").Range("J:J").SpecialCells(xlConstants)
For Each Dt In DtRNG

If Dt = "Yes" Then

Let I = Dt.Row
Let ID = Range("G" & I & "")
Let Program = Range("B" & I & "")
Let Root = Range("E" & I & "")
Let Action = Range("F" & I & "")
Let resolv = Range("I" & I & "")

Set wApp = CreateObject("word.application")
wApp.Visible = True
'Set wDoc = wApp.Documents.Add ' to add a brand new document instead
wApp.Documents.Open "C:\.docx" 'put your directory and document name and extension here
wApp.Selection.Delete Unit:=wdCharacter, Count:=1
wApp.Selection.Delete Unit:=wdCharacter, Count:=1
wApp.Selection.Delete Unit:=wdCharacter, Count:=1
wApp.Selection.Delete Unit:=wdCharacter, Count:=1
wApp.Selection.Delete Unit:=wdCharacter, Count:=1
wApp.Selection.Delete Unit:=wdCharacter, Count:=1
wApp.Selection.Delete Unit:=wdCharacter, Count:=1
wApp.Selection.Delete Unit:=wdCharacter, Count:=1
wApp.Selection.Delete Unit:=wdCharacter, Count:=1
wApp.Selection.Delete Unit:=wdCharacter, Count:=1
wApp.Selection.Delete Unit:=wdCharacter, Count:=1
wApp.Selection.Delete Unit:=wdCharacter, Count:=1
wApp.Selection.Delete Unit:=wdCharacter, Count:=1
wApp.Selection.Delete Unit:=wdCharacter, Count:=1
wApp.Selection.Delete Unit:=wdCharacter, Count:=1
wApp.Selection.Delete Unit:=wdCharacter, Count:=1
wApp.Selection.Delete Unit:=wdCharacter, Count:=1
wApp.Selection.Delete Unit:=wdCharacter, Count:=1
wApp.Selection.Delete Unit:=wdCharacter, Count:=1

wApp.Selection = "Issue: " & ID & ""
wApp.Selection.MoveRight Unit:=wdCell
wApp.Selection = "Root cause: " & Root & ""
wApp.Selection.MoveRight Unit:=wdCell
wApp.Selection = "Action: " & Action & ""
wApp.Selection.MoveRight Unit:=wdCell
wApp.Selection = "Resolver: " & resolv & ""
Application.DisplayAlerts = False
wApp.Documents.Save
wApp.Quit
Application.DisplayAlerts = True

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = ""
        .Body = ""
        .Attachments.Add ("C:\.docx") 'put your directory and document name and extension here
        .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End If
            
Next Dt

End Sub

Hi jessebranum777,

Sorry for the delay..thanks alot for the help...I changed the code as per my requirement by creating the required design of the mail body in another sheet of the same workbook and then populating it with the required data.

I could successfully create the mail with all the data required.Thanks alot for all the support

Awesome! If you haven't already give rep to those whom helped and mark the thread as solved.