sending excel sheet via outlook

[DOF2001]

Hello,

I am tryingo to write macro that sends an auto email everytime the user clicks a button, i found some code for this part but i also need to be able to send the e-mail to a specific person based on what is selected on cell D4 as well as to giv ethe user the oportunity to add any context on hte bopdy of the e-mail.

Sub DoALLsingle()

    'Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim Mail_Object, Email_Subject, o As Variant
    
    Set Destwb = ActiveWorkbook

    Set Mail_Object = CreateObject("Outlook.Application")
        With Mail_Object.CreateItem(o)
            .Subject = " Request" ' CHANGE TO SUIT
            .To = "sss@ddd.com" 'CHANGE TO SUIT
            '.Body = "E MAIL TEXT GOES HERE" & Chr(13) & Chr(13) & "Regards," & Chr(13) & "YOUR NAME." & Chr(13) & "YOUR ADDRESS." 'Change comments to suit
            .Attachments.Add Destwb.FullName
            .Body = "Thank you" & Chr(13) & Chr(13) '& "Regards," & Chr(13) & "YOUR NAME." & Chr(13) & "YOUR ADDRESS." 'Change comments to suit
            '.send
    End With
        'MsgBox "E-mail successfully sent", 64
        'Application.DisplayAlerts = False
Set Mail_Object = Nothing

    Cells.Select
    Selection.EntireRow.Hidden = False
    Range("E11:I31,E9,G33:I33,E4").Select
    Range("E4").Activate
    Selection.ClearContents

'ActiveWorkbook.Save
'ActiveWorkbook.Close

End Sub

 

[Dartagnan]

Just curious. Why not use Excels built in functionality?

 

[Maheshp]

Check whether this help
[URL]http://www.rondebruin.nl/sendmail.htm[/url]

 

[DOF2001]

Thank you

dartagnan, but i'm not familiar with those functionalities you mentioned
Maheshp i can access the link you mentioned?

 

[Dartagnan]

Depending on your version, go to File then select send or save & send. Once there you will see the option to send the workbook via email. Also, the email program will open and allow you to add to the body of the message.

 

[DOF2001]

Thank you Dartagnan, but in htis case it won't work becasue the user won't know where to send it. i need a macro that will send the e-mail to a specific person based on input from the worksheet

 

[Dartagnan]

This should at least get you started.

Sub email()

Dim myoutlook As Object
Dim mymail As Object
Set otlapp = CreateObject("Outlook.Application")
Set otlnewmail = otlapp.createitem(olMailItem)

With otlnewmail
.to = Worksheets("sheet3").Range("d4")
.Subject = "Test"
.Body = "Attached is my test"
.Attachments.Add ActiveWorkbook.FullName

.display
End With

End Sub