Bring up Email when button clicked on spreadsheet

[surkdidat]

Is it possible to have an automatic email be created when a button is pressed on a spreadsheet. I will need a box to come up asking for a reference number (which is in Column A).

We this is input I need a set email address in CC (ie aa@aaa.com), To field to be blank. The subject field should be the reference number input, and the equivalent value in column B (for that row) (So reference A1000 is input into the pop up box, this is in cell A5, the subject therefore would be A5 and B5.

(Also with a set message, and if possible voting buttons)


Extra if poss - to have the next blank cell in that row to timestamp (columns G-R )

 

[Logit]

.
https://www.rondebruin.nl/win/s1/outlook/mail.htm

 

[surkdidat]

Thank you for this - however, I need to, when a button is clicked to run the code, to bring up a MsgBox asking for a reference number (which will be in Column B)

When this is input, the subject of the email will be that of the reference number, and the text also contained in the equivalent cell in column B.

So, if A5 equals A1004, and this is what is input into the text box then the subject line is populated with A1004, and the text in B5.

Many thanks

.
https://www.rondebruin.nl/win/s1/outlook/mail.htm

 

[surkdidat]

Further to add - I have now modified my code as below, so what is input into a inputbox populates the subject heading:

What I need now, is whatever is input is to look that up in the spreadsheet in column A, and populate the data in the adjacement cell in column B into the subject header aswell. (I also need to work out how I can timestamp it into that row in columns G-R in the first available cell

Sub Mail_Send()


    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
pwd1 = InputBox("Please Enter the password")


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


    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"


    On Error Resume Next
    With OutMail
        .To = ""
        .CC = "aaaa@aa.com"
        .BCC = ""
        .Subject = pwd1
        .Body = strbody
        
        .Display
    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub