search

MicrosoftDocs / feedback

📢 docs.microsoft.com site feedback
https://learn.microsoft.com
Creative Commons Attribution 4.0 International
239 stars 160 forks source link

Excel VBA to Outlook Email Bug - Filtered excel data coming as hidden data in outlook email #1470

Closed Cherianpaul closed 5 years ago

Cherianpaul commented 5 years ago

I am trying to send some emails to specific users on process deviations using excel VBA code. What I do is first apply filter for a user and copy all of the issues in his name and make an HTML and copy that html code as NewEmail.HTMLBody. Result is, while sending the email, my outlook "sent item" shows only entries for that user as a table but, internally other user entries are hidden. This will get revealed if that user reply to that email. All hidden entries in the table will be visible then. The code I used is below

'Save the temp worksheet as a HTML file
Set objOutlookApp = CreateObject("Outlook.Application")
On Error GoTo 0
For Each key In counter.Keys 'unique user names from the excel table for filtering for each user's deviation issues
    objTempWorksheet.Range("A1" & ":Q" & lastrow).AutoFilter Field:=2, Criteria1:=key, Operator:=xlFilterValues
    If objTempWorksheet.Range("A" & Rows.Count).End(xlUp).Row = 1 Then GoTo DevEmailErrHand
    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 objNewEmail = objOutlookApp.CreateItem(olMailItem)

   'Read the HTML file data and insert into the email body
   Set objTextStream = objFileSystem.OpenTextFile(strTempHTMLFile)
   objNewEmail.HTMLBody = "Hello " & key & "," & "<br>" & objTextStream.ReadAll & "<br>" & "Regards" & "<br>" & pm_name
   objNewEmail.Display
   'You can specify the new email recipients, subjects here using the following lines:
   objNewEmail.To = key '"name@domain.com"
   objNewEmail.Subject = "Deviation Alert"
   objTextStream.Close
   objFileSystem.DeleteFile (strTempHTMLFile)
   'objNewEmail.Send --> directly send out this email
Next key
welcome[bot] commented 5 years ago

Thank you for creating the issue! One of our team members will get back to you shortly with additional information.

ryanmajidi commented 5 years ago

@Cherianpaul Please contact support here: https://support.office.com/en-us/excel