Vba to include values of specific cells selection to the subject of outlook mail

[Waleed_wwn]

I select data from excel workbook and send to outlook. I need to update the below Vba code to include values of specific cells in columns (F,G,H) corresponding to my selection to be in Subject of Email , but I do not know how !

For example: Work orders need review @” ……… , …………, ………….

[![Sheet Data][1]][1]


Sub Send_Selections_TutlookEmail()

Dim objSelection As Excel.Range
Dim objTempWorkbook As Excel.Workbook
Dim objTempWorksheet As Excel.Worksheet
Dim strTempHTMLFile As String
Dim objTempHTMLFile As Object
Dim objFileSystem As Object
Dim objTextStream As Object
Dim objOutlookApp As Outlook.Application
Dim objNewEmail As Outlook.MailItem
Dim Strbody As String


'Set the selection
Set objSelection = Nothing
Set objSelection = Selection.SpecialCells(xlCellTypeVisible)
Selection.Copy

'Paste the copied selected ranges into a temp worksheet
Set objTempWorkbook = Excel.Application.Workbooks.Add(1)
Set objTempWorksheet = objTempWorkbook.Sheets(1)

'Keep the values, column widths and formats in pasting
With objTempWorksheet.Cells(1)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteColumnWidths
.PasteSpecial xlPasteFormats
End With

'Save the temp worksheet as a HTML file
Set objFileSystem = CreateObject("Scripting.FileSystemObject")
strTempHTMLFile = objFileSystem.GetSpecialFolder(2).Path & "\Temp for Excel" & Format(Now, "YYYY-MM-DD hh-mm-ss") & ".htm"
Set objTempHTMLFile = objTempWorkbook.PublishObjects.Add(xlSourceRange, strTempHTMLFile, objTempWorksheet.name, objTempWorksheet.UsedRange.Address)
objTempHTMLFile.Publish (True)

'Create a new email
Set objOutlookApp = CreateObject("Outlook.Application")
Set objNewEmail = objOutlookApp.CreateItem(olMailItem)

'Read the HTML file data and insert into the email body
objNewEmail.Display
Set objTextStream = objFileSystem.OpenTextFile(strTempHTMLFile)
Strbody = "<H5>Eng.</H5>" & "Kindly review the below item to close.<br>"

objNewEmail.HTMLBody = Strbody & "<table align=""left"">" & objTextStream.ReadAll & "<br>" & "<br>" & objNewEmail.HTMLBody

'You can specify the new email recipients, subjects here using the following lines:
'objNewEmail.To = "johnsmith@datanumen.com"
objNewEmail.Subject = " PM need review to close @"
'objNewEmail.Send --> directly send out this email

objTextStream.Close
objTempWorkbook.Close (False)
objFileSystem.DeleteFile (strTempHTMLFile)

End Sub


[1]:

https://imgur.com/DvINc

 

[dnorm]

I hope this helps:
I have a CONCAT formula in AN4 to group my data for the subject line and reference in the mail.

Set dam = CreateObject("Outlook.Application").CreateItem(0)
  dam.To = "[EMAIL]johnsmith@datanumen.com[/EMAIL]"          'Put your emails here
  dam.Cc = "[EMAIL]johnsmith@datanumen.com[/EMAIL]"          'Put your emails here to CC
  dam.Subject = "Items requiring closure.<br>" & Range("AN4")                'Range AN4 to be location of items to be reviewed
  dam.Body = "Good Morning" & vbCr & vbCr & _
             "Kindly review the below item to close.<br>" & Range("AN4")
  dam.Attachments.Add Name 'Delete this line if no attachments needed. Define Name if attachments wanted
  dam.Display
  'Switch to .Send to send the mail .Display to view email

 

[Waleed_wwn]

"Niton" from stackoverflow , Helpfully create this VBA and it works ?:

Option Explicit 

Sub Append_GHIValues_To_Subject()
    
    Dim objOutlookApp As Outlook.Application
    Dim objNewEmail As Outlook.MailItem
    
    Dim rwSel As Range

    Dim GValue As String
    Dim HValue As String
    Dim IValue As String
    
    Set rwSel = Selection
    
    With rwSel.EntireRow
        GValue = .Cells(6).Value
        HValue = .Cells(7).Value
        IValue = .Cells(8).Value
    End With
    
    'Create a new email
    Set objOutlookApp = CreateObject("Outlook.Application")
    Set objNewEmail = objOutlookApp.CreateItem(olMailItem)

    objNewEmail.Display
    objNewEmail.Subject = " PM need review to close @" & " " & GValue & " " & HValue & " " & IValue
            
End Sub