Send email depending on cell values in selected row

Hi, not sure if this is possible or not.....?

I am after a macro which I can assign to a custom toolbar, which will:

Open a new outlook mail, with a standard subject line and message
Populate the To box depending on the value of a certain cell within a selected row.

If it can be really flash I also would like it to include a hyperlink or file attachment by looking in a specific folder and recognising the file name with the same ref as in another cell of the selected row.

See attached example.


Any help will be much appricated. Even if its a give up it can't be done!

Thanks

Yes, that is possible.

Dummy code which you can modify as per your requirements :

Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

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

    On Error Resume Next
    With OutMail
        .To = "me@here.com"
        .CC = ""
        .BCC = ""
        .Subject = "The Filefor #" & Chr(32) & Range("H4") & " has been updated."
        .Body = "Please review." & vbCrLf & vbCrLf & ActiveWorkbook.FullName
        .Send   'or use .Display
        .ReadReceiptRequested = True
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing
    
    Dim Msg, Style, Title
        Msg = "E-mail has been sent " & Chr(13) & Chr(10) & "Press OK to continue."
        Style = vbOKOnly + vbInformation
        Title = "Open Issues List"
        Response = MsgBox(Msg, Style, Title, Help, Ctxt)

I assume that you have enough knowledge of VBA to modify it as per your requirements. If not, please let me know, I will help you out.

The attachment is also possible. You can use FileSystemObject, or Application.FileSearch as well.

Let me know if you need more help.

Thanks,
Vikas

Hi Vikas, thanks for your help.

I am very new to VBA and am teaching myself as I go along.

Although I kind of understand the code, I am confused as to where the cell selection comes in to determine who the email goes to.

Any further explaination gratefully received.

Roxanne