Create email from excel with good morning/afternoon depending on time

[jmcconnell]

Hi,

Looking to have a button within a spreadsheet that creates an email using outlook. Rather than just saying "Hello", I'd like it to say good morning up until 12:00, good afternoon until 17:00 and good evening. I'd also like to insert a signature at the bottom. Here's what I have so far:

Private Sub Beenageeha_Click()


    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    On Error Resume Next
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hi," & vbNewLine & vbNewLine & _
              "Please see the fault below:" & vbNewLine & vbNewLine & _
              "Kind regards,"
                  On Error Resume Next
    With xOutMail
        .To = ""
        .CC = "insertemail@email.com; insertemail@email.com"
        .BCC = ""
        .Subject = "Beenageeha fault"
        .Body = xMailBody
        .Display
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
End Sub

Any help is much appreciated.
Thanks in advance,
James.

 

[Peter h]

If Time < TimeValue("12:00:00") Then
xMailBody = "Good Morning"...
ElseIf Time > TimeValue("12:00:00") And Time < TimeValue("17:00:00") Then
xMailBoxy = "Good Afternoon"...
Else 
xMailBody = "Good Evening"...
End If

Haven't tested this, so play around with it. You're basically setting your string depending on if the current times meets certain criteria.

 

[jmcconnell]

Thanks. That worked perfectly.

I've now added to the code so it pastes the contents of the clipboard (This is a picture from using the snipping tool) into an email. The problem i'm now having is getting the picture to display a couple of lines below the text. Just now is always appears to the right. How can I move it down a line or 2.

Thanks again for the help.

 

[Peter h]

I haven't done much coding to send out emails, so I'm not sure if this is correct, but try adding a couple of vbNewLines at the end of the string. So:

xMailBody = "your message here" & vbNewLine & vbNewLine