+ Reply to Thread
Results 1 to 17 of 17

Excel VBA to automatically add Cell Value(s) to Outlook Subject Line

  1. #1
    Registered User
    Join Date
    05-21-2013
    Location
    Elkton, MD
    MS-Off Ver
    365
    Posts
    32

    Question Excel VBA to automatically add Cell Value(s) to Outlook Subject Line

    Hello – First, I am letting you know that I have this question posted on the MrExcel.com forum, but have had no responses, so I'm hoping this forum may be a better fit for my questions.

    I have a spreadsheet with columns “A thru AG” and ~1000 rows where I need to send daily emails for all late orders. I am currently using Ron De Bruins macro where it loops through the spreadsheet and pulls all the unique email addresses from Column “D” and copies them to a new worksheet and then sends an email with all the late order information using the “Range to html” macro.

    I also want to pull the order number from column "A" and add the value to the subject line. I'm able to get the first instance with " .Subject = "Follow-up Regarding PO " & Ash.Cells(Rnum, 1).Value", however, it is not pulling the correct order number with the unique filter on the email addresses. Can anyone help me with being able to have the order numbers added to the subject line that matches the email address for each (also add multiple order numbers for those that have more than 1? I've stepped through the code line by line, but still can not figure it out...I'm truly at a loss with this

    I am also looking to have the date sent added to the excel spreadsheet in column "F" once the email has been sent.

    For some reason, I am not able to add an attachment to this, so I'll post, then try to attach. Below is my code.

    VBA Code:
    Sub Send_Row_Or_Rows_2()

    'For Tips see: 'http://www.rondebruin.nl/win/winmail/Outlook/tips.htm' Excel Automation - Ron de Bruin
    'Don't forget to copy the function RangetoHTML in the module.
    'Working in Excel 2000-2016
    Dim OutApp As Object
    Dim OutMail As Object
    Dim rng As Range
    Dim Ash As Worksheet
    Dim Cws As Worksheet
    Dim Rcount As Long
    Dim Rnum As Long
    Dim FilterRange As Range
    Dim FieldNum As Integer
    Dim DefaultSignature As String

    On Error GoTo cleanup

    Set OutApp = CreateObject("Outlook.Application")

    With Application
    .EnableEvents = False
    .ScreenUpdating = False

    End With

    'Set filter sheet, you can also use Sheets("MySheet")
    Set Ash = ActiveSheet
    'Set filter range and filter column (column with e-mail addresses)
    Set FilterRange = Ash.Range("A1:AG" & Ash.Rows.Count)
    FieldNum = 4 'Filter column = D because the filter range start in column A

    'Add a worksheet for the unique list and copy the unique list in A1
    Set Cws = Worksheets.Add
    FilterRange.Columns(FieldNum).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=Cws.Range("A1"), _
    CriteriaRange:="", Unique:=True

    'Count of the unique values + the header cell
    Rcount = Application.WorksheetFunction.CountA(Cws.Columns(1))

    'If there are unique values start the loop
    If Rcount >= 2 Then
    For Rnum = 2 To Rcount

    'Filter the FilterRange on the FieldNum column (column 4)
    FilterRange.AutoFilter Field:=FieldNum, _
    Criteria1:=Cws.Cells(Rnum, 1).Value

    'If the unique value is a mail address create a mail
    If Cws.Cells(Rnum, 1).Value Like "?*@?*.?*" Then

    'Copy the visible data in a new workbook
    With Ash.AutoFilter.Range
    On Error Resume Next
    Set rng = .SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    End With

    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next

    With OutMail
    .to = Cws.Cells(Rnum, 1).Value
    .Subject = "Follow-up Regarding PO" & Ash.Cells(Rnum, 1).Value
    .Display 'Or use Send
    .HTMLBody = "Hello - I'm following up on late orders, and before I contact the vendor, I wanted to check in with you to see if you received and/or completed the following orders. Thank you" & _
    RangetoHTML(rng) & .HTMLBody

    End With

    On Error GoTo 0
    Set OutMail = Nothing

    End If

    'Close AutoFilter

    Ash.AutoFilterMode = False

    Next Rnum

    End If

    MsgBox "Email has sent successfully!"

    cleanup:
    Set OutApp = Nothing
    Application.DisplayAlerts = False
    Cws.Delete
    Application.DisplayAlerts = True

    With Application
    .EnableEvents = True
    .ScreenUpdating = True

    End With

    End Sub

    Function RangetoHTML(rng As Range)
    ' Changed by Ron de Bruin 28-Oct-2006
    ' Working in Office 2000-2016
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook

    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
    .Cells(1).PasteSpecial Paste:=8
    .Cells(1).PasteSpecial xlPasteValues, , False, False
    .Cells(1).PasteSpecial xlPasteFormats, , False, False
    .Cells(1).Select
    Application.CutCopyMode = False
    On Error Resume Next
    .DrawingObjects.Visible = True
    .DrawingObjects.Delete
    On Error GoTo 0
    End With

    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
    SourceType:=xlSourceRange, _
    Filename:=TempFile, _
    Sheet:=TempWB.Sheets(1).Name, _
    Source:=TempWB.Sheets(1).UsedRange.Address, _
    HtmlType:=xlHtmlStatic)
    .Publish (True)
    End With

    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.readall
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
    "align=left x:publishsource=")
    'Close TempWB
    TempWB.Close savechanges:=False

    'Delete the htm file we used in this function
    Kill TempFile
    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
    End Function

  2. #2
    Registered User
    Join Date
    05-21-2013
    Location
    Elkton, MD
    MS-Off Ver
    365
    Posts
    32

    Re: Excel VBA to automatically add Cell Value(s) to Outlook Subject Line

    Hopefully my attachment show up here :-)
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    05-21-2013
    Location
    Elkton, MD
    MS-Off Ver
    365
    Posts
    32

    Re: Excel VBA to automatically add Cell Value(s) to Outlook Subject Line

    Hello - Is there anyone who can help me with adding a column (A) in the subject line that is dependent on the email address?

  4. #4
    Registered User
    Join Date
    05-21-2013
    Location
    Elkton, MD
    MS-Off Ver
    365
    Posts
    32

    Question Re: Excel VBA to automatically add Cell Value(s) to Outlook Subject Line

    Good Morning - It's been a few months, but I have made some progress with the code:-) I have been able to get the code to work...mostly. However, I'm still struggling with:
    1: Copying cell value from column A to email Subject Line - I am looking for unique values only (it is currently pulling in AA the values)
    2: I need to add a .cc line to copy email recipients from (column AB) that match each order number row (column A). I also need this to be unique values. Here is my code so far and an example of an email it displays.

    Please Login or Register  to view this content.
    2022-02-24_10-18-51.png

    I'm hoping someone can please show me what I am doing wrong! Thanks.

  5. #5
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Excel VBA to automatically add Cell Value(s) to Outlook Subject Line

    Try this code

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    05-21-2013
    Location
    Elkton, MD
    MS-Off Ver
    365
    Posts
    32

    Re: Excel VBA to automatically add Cell Value(s) to Outlook Subject Line

    maniacb - You are a genius! I have been working on this since Nov/Dec and could not figure it out! I know you cannot see me, but I'm doing the happy dance right now...I cannot thank you enough, this is awesome

    One change, if possible - In the body of the email, there is a snapshot of the order numbers along with line numbers, so I would like to have the subject line to only pull in the unique order numbers. Also, is there a way to remove the comma at the end of the subject line? Here is a sample of the email!
    Attached Images Attached Images

  7. #7
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Excel VBA to automatically add Cell Value(s) to Outlook Subject Line

    Here you go.

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    05-21-2013
    Location
    Elkton, MD
    MS-Off Ver
    365
    Posts
    32

    Re: Excel VBA to automatically add Cell Value(s) to Outlook Subject Line

    Yes, this works :-) The only issue now is that my signature is not pulling in correctly. I tried to comment out the DefaultSignature = OutMail.Body line and add the .htmlbody back to the end of the body line, but when I do this, it brings back the last comma in the subject line.
    This is how it is pulling the signature in:
    2022-02-26_9-59-18.png

    But am looking to have it added as normal:
    2022-02-26_10-00-28.png

    Is this possible?

  9. #9
    Registered User
    Join Date
    05-21-2013
    Location
    Elkton, MD
    MS-Off Ver
    365
    Posts
    32

    Re: Excel VBA to automatically add Cell Value(s) to Outlook Subject Line

    I was able to get it to work!!! maniacb, I can't thank you enough for all of your help! You have no idea how much time this will save me each day! Thank you, again :-)

  10. #10
    Registered User
    Join Date
    05-21-2013
    Location
    Elkton, MD
    MS-Off Ver
    365
    Posts
    32

    Re: Excel VBA to automatically add Cell Value(s) to Outlook Subject Line

    Good Morning maniacb - I am so sorry, but I still can not remove the last comma in the Subject Line. I tried to comment out the DefaultSignature = OutMail.Body line and add the .htmlbody back to the end of the body line for my default signature, but when I do this, it brings back the last comma in the subject line.
    Can you please let me know what section needs to be updated or changed? Thanks.

    Please Login or Register  to view this content.

  11. #11
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Excel VBA to automatically add Cell Value(s) to Outlook Subject Line

    Try this update. I added the defaultsignature at the end of the body

    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    05-21-2013
    Location
    Elkton, MD
    MS-Off Ver
    365
    Posts
    32

    Re: Excel VBA to automatically add Cell Value(s) to Outlook Subject Line

    Good Morning - It pulled in my signature but as plain text. I'm looking to have it pull in as my signature does when I send an email with my name, address, phone, email (which has a link). Hopefully this makes sense! Also, I am trying to put a space between the comma's.

    2022-03-04_6-50-07.png

    --------------------------------------------------
    This is my normal signature when sending an email. Sorry I had to block out my personal information.

    2022-03-04_6-53-29.png

  13. #13
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Excel VBA to automatically add Cell Value(s) to Outlook Subject Line

    Change this line

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    05-21-2013
    Location
    Elkton, MD
    MS-Off Ver
    365
    Posts
    32

    Re: Excel VBA to automatically add Cell Value(s) to Outlook Subject Line

    Perfect...that worked I'm still struggling with adding a space between the comma's in the subject line? I've tried adding a space, but then I end up getting the comma at the very end where I'm trying to remove the trailing comma. I also tried to adjust the Right/Left len line with -2 instead of -1, but that still doesn't change it?

  15. #15
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,103

    Re: Excel VBA to automatically add Cell Value(s) to Outlook Subject Line

    Change the following, from
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    and from
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    05-21-2013
    Location
    Elkton, MD
    MS-Off Ver
    365
    Posts
    32

    "SOLVED" Re: Excel VBA to automatically add Cell Value(s) to Outlook Subject Line

    YES!!!! Thank you so much...I never would have completed this without you! In looking at the code, I realized that I only changed the left len to -2 (not both). I truly appreciate all of your patience and help
    Last edited by kmclaugh; 03-09-2022 at 10:18 AM.

  17. #17
    Registered User
    Join Date
    05-21-2013
    Location
    Elkton, MD
    MS-Off Ver
    365
    Posts
    32

    Re: Excel VBA to automatically add Cell Value(s) to Outlook Subject Line

    Hello again - For some reason, the Function RangetoHTML is no longer working. I keep getting a "compile error". Does anyone know what would be wrong? It keeps highlighting the "format" text.
    CompileError.png

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. excel cell info extracted to outlook subject line as dragged in or added
    By snkhan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-05-2021, 09:31 PM
  2. Foreign language or symbols in Outlook subject line when mailed using macro in Excel
    By go3go3go in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-29-2020, 11:26 AM
  3. Autofill outlook subject line from excel sheet
    By Dancon in forum Outlook Formatting & Functions
    Replies: 9
    Last Post: 04-27-2020, 03:22 AM
  4. Autofill outlook subject line from excel sheet
    By Dancon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-26-2020, 06:26 PM
  5. VBA Code - Extracting Outlook Subject line text into various Excel columns
    By MHamid in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-20-2017, 12:24 PM
  6. Replies: 0
    Last Post: 06-18-2015, 05:35 PM
  7. Outlook Subject line from Excel Cell reference
    By Shibu.excel in forum Outlook Formatting & Functions
    Replies: 4
    Last Post: 09-06-2012, 03:19 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1