Help with code - Inserting values from cells in a spreadsheet in an email message.

[Jay3]

Hi Everyone,

This should be a relitively straight forward question for anyone who know VBA.

I need to amend the following code so that when the email is composed but inserts values from cells in the spreadsheet?

Can anyone tell me how to do this?

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "Cell A1 is changed" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"
    On Error Resume Next
    With OutMail
        .To = "[EMAIL="emailaddresshere@somewher.co.uk"]emailaddresshere@somewher.co.uk[/EMAIL]"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

The bit of the code I need to amend is shown below. I want to be able to insert the value from B2, B3, B4 for example.

 "Cell A1 is changed" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

Can someone also tell me how to get the email to send automatically rather than just be drafted for the user to then hit send?

Many thanks,
Jay3

 

[VoG]

Try

strbody = "Hi there" & vbNewLine & vbNewLine & _
              "Cell A1 is changed" & vbNewLine & _
              Range("B2").Value & vbNewLine & _
              Range("B3").Value & vbNewLine & _
              Range("B4").Value

And change .Display to .Send.

 

[Jay3]

Hi....thanks for that really helpful.

Can you tell me what to do if I want to concatenate.

For example.....


"A new request has been added to the list by [B2] the deadline is [C2]" etc...

Thanks,
Jay3

 

[VoG]

Assuming that C2 contains a date try like this

strBody = "A new request has been added to the list by " & Range("B2").Value & " the deadline is " & Format(Range("C2").Value, "dd/mm/yyyy")

 

[Ruddles]

I need to amend the following code so that when the email is composed but inserts values from cells in the spreadsheet?

Ah, I emailed you last night (11pm UK time, it's 9.30am here now).

Can someone also tell me how to get the email to send automatically rather than just be drafted for the user to then hit send?

        .Display [COLOR=green] 'or use .Send[/COLOR]

 

[Jay3]

Thanks this is really helpful!

As this is a task list can you help be tweak the code so that it looks at the most recently added row each time?

For eaxmple if someone adds a new task on row 3 I will need the cell reference to update accordingly.

This is the code so far.

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    strbody = "Dear John" & vbNewLine & vbNewLine & _
              "A new request has been added to the list by " & Range("B2").Value & _
              " the aspirational deadline is " & Format(Range("E2").Value, "dd/mm/yyyy") & _
              " the external deadline is " & Format(Range("F2").Value, "dd/mm/yyyy") & vbNewLine & _
              " Request Title: " & Range("C2").Value & vbNewLine & _
              " Request Description: " & Range("D2").Value
              
                           
    On Error Resume Next
    With OutMail
        .To = "[EMAIL="emailaddresshere@somewher.co.uk"]emailaddresshere@somewher.co.uk[/EMAIL]"
        .CC = ""
        .BCC = ""
        .Subject = "New Task Added to Request Listing Today"
        .Body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Anyhelp is much appreciated.

Thanks,
Jay3

 

[VoG]

Try

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim LR As Long
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    LR = Range("B" & Rows.Count).End(xlUp).Row
    strbody = "Dear John" & vbNewLine & vbNewLine & _
              "A new request has been added to the list by " & Range("B" & LR).Value & _
              " the aspirational deadline is " & Format(Range("E" & LR).Value, "dd/mm/yyyy") & _
              " the external deadline is " & Format(Range("F" & LR).Value, "dd/mm/yyyy") & vbNewLine & _
              " Request Title: " & Range("C" & LR).Value & vbNewLine & _
              " Request Description: " & Range("D" & LR).Value
              
                           
    On Error Resume Next
    With OutMail
        .To = "emailaddresshere@somewher.co.uk"
        .CC = ""
        .BCC = ""
        .Subject = "New Task Added to Request Listing Today"
        .Body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub