Excel Email macro

[JohnOB]

Hi All

Hope someone can help me with the below I have been looking for examples of code to try and help but with no luck.

I want to have a button in a spreadsheet which when clicked will open a new email.

I do not want the file attached to the email, however I would like the contents of some of the cells to appear as part of the subject and body, I would also like the full file path to appear as part of the body.

The idea is that when someone completes a part of the spreadsheet they hit this button and it creates an email to let someone know the spreadsheet has been updated and the location of it. The person who gets the email will always be the same person.

All examples I can find show me how to attach the sheet or workbook which I don't want to do, or they use Outlook which we do not use (Lotus Notes) so need a generic new mail command and not any sort of outlook object.

Any advice appreciated.

Thanks

John

"Stupid isn't not knowing the answer, it's not asking the question

 

[SkipVought]

Hi,

What code do you have so far? Where are you stuck?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[clouddog9]

I found this code on the net (some where)

Sub Send_Email_Using_VBA()

    Dim strSubject, strSendFrom, strBody, strFName As String
    Dim strSendTo As String
    Dim strLinkPath, strMatlFile, strFileName As String
    strMatlFile = Sheets("Lookup").Range("D2") & " Materials Expense.xlsm"
    strFilePath = Sheets("Lookup").Range("D4")
    Dim otlMailObject, otlMailSingle As Variant
    strSubject = "Subject"
    strSend_From = "From"
    strSendTo = "To"
    strBody = "Body"
    strFName = "path with file name"
    Workbooks(strMatlFile).Close
    On Error GoTo debugs
    Set otlMailObject = CreateObject("Outlook.Application")
    Set otlMailSingle = otlMailObject.CreateItem(0)
    With otlMailSingle
        .Subject = strSubject
        .To = strSendTo
        .Body = strBody
        .Attachments.Add strFName
        .send
    End With
debugs:
    If Err.Description <> "" Then MsgBox Err.Description
End Sub

 

[bubba100]

Take a look at Thread707-1463407