Choose from two subjects for mail based on entry in cell

user_16981638 0
Viewed 59

I need to make this condition for mail subject:
If the time in cell S5 != 00:00 then put the text which is in cell S5.
Else put the text which is in cell T5.

Sub esendtable()

Dim outlook As Object
Dim newEmail As Object
Dim xInspect As Object
Dim pageEditor As Object

Set outlook = CreateObject("Outlook.Application")
Set newEmail = outlook.CreateItem(0)
Set xInspect = newEmail.GetInspector
Set pageEditor = xInspect.WordEditor

With newEmail
    .To = Sheet1.Range("S2").Text
    .CC = Sheet1.Range("S3").Text
    .BCC = ""
    .Subject = Sheet1.Range("S5").Text
    .Body = "Dear All," & vbNewLine & vbNewLine & vbTab & Sheet1.Range("S6").Text
    .Recipients.ResolveAll
    .display
        
    Sheet1.Range("B2:L12").Copy
      
    pageEditor.Application.Selection.Start = Len(.Body)
    pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
    pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)
    .display
    
    Set pageEditor = Nothing
    Set xInspect = Nothing
End With

Set newEmail = Nothing
Set outlook = Nothing

End Sub
outlook vba excel
1 Answers

Please use if-else condition. If S5 = 00:00 then the subject will be value of T5 cell else it will be value of S5 cell.

Condition:

If subject = "00:00" Then

subject = Sheet1.Range("T5").Text

Else

subject = Sheet1.Range("S5").Text

End If

Complete code:

Sub esendtable()

Dim outlook As Object

Dim newEmail As Object

Dim xInspect As Object

Dim pageEditor As Object

Set outlook = CreateObject("Outlook.Application")

Set newEmail = outlook.CreateItem(0)

Set xInspect = newEmail.GetInspector

Set pageEditor = xInspect.WordEditor

Dim subject As String

subject = Sheet1.Range("S5").Value

If subject = "00:00" Then

subject = Sheet1.Range("T5").Text

Else

subject = Sheet1.Range("S5").Text

End If

With newEmail

    .To = Sheet1.Range("S2").Text
    .CC = Sheet1.Range("S3").Text
    .BCC = ""
    .subject = subject
    .Body = "Dear All," & vbNewLine & vbNewLine & vbTab & Sheet1.Range("S6").Text
    .Recipients.ResolveAll
    .display
        
    Sheet1.Range("B2:L12").Copy
      
    pageEditor.Application.Selection.Start = Len(.Body)
    pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
    pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)
    .display
    
    Set pageEditor = Nothing
    Set xInspect = Nothing
End With

Set newEmail = Nothing

Set outlook = Nothing

End Sub
user_77316230
Related