Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sending personalized email from Excel (2007)

    Hello all.

    I have a spreadsheet I use to notify customers. It works fantastic and does everything I want except for one thing.

    I've attached the spreadsheet and here is the code:

    <hr>Sub PartsNotif()

    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim cell As Range
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    On Error GoTo cleanup
    For Each cell In Sheets("Order Sheet").Columns("B").Cells.SpecialCells(xlCellType Constants)
    If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" Then
    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
    .To = cell.Value
    .CC = ""
    .Subject = "All Parts Order Confirmation Order # " & cell.Offset(0, 3).Value
    .HTMLBody = "All Parts has received your order." & _
    "Date of Invoice: " & cell.Offset(0, -1) & _
    "Customer Name: " & cell.Offset(0, 4) & _
    "Address: " & cell.Offset(0, 2) & _
    "Part ID: " & cell.Offset(0, 5) & _
    "Part Description: " & cell.Offset(0, 6) & _
    "All-Parts Order#: " & cell.Offset(0, 3) & _
    "John Doe" & _
    "Parts Specialist" & _
    "All Parts" & _
    "800-ALL-PARTS" & _
    "M-F (8:00AM - 5:00PM CDT)"
    .Display
    End With
    Set OutMail = Nothing

    End If
    Next cell
    cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True

    End Sub<hr>

    This works exactly the way I want, but only for customers who have ordered only one part.

    Is there a way to modify this to where if a customer has more than one part that the information is transferred into the email? I've been able to run additional cells to the right and fill them accordingly and have the code grab those, but that's rather cumbersome plus it only grabs a set number of cells which gives me a bunch of blank space if I don't have data for them all.

    If I can get it to send an email to multiple customers with multiple parts at once, that would save so much time.

    Right now I can send to multiple customers all at once, but only if they have ordered only one part.

    Any suggestions?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sending personalized email from Excel (2007)

    How would you prefer to organize the data? Multiple rows for the same customer if they have ordered more than one item, or extra columns?

  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sending personalized email from Excel (2007)

    Multiple rows for the same customer would be ideal.

    I never know how many parts will be on the same order. Anywhere from one to 50 or more.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sending personalized email from Excel (2007)

    Try this version:
    <code>
    Sub PartsNotif()
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim cell As Range
    Dim cell2 As Range
    Dim m As Long
    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    On Error GoTo Cleanup
    m = Sheets("Order Sheet").Range("B" & Rows.Count).End(xlUp).Row
    Set cell = Sheets("Order Sheet").Range("B2")
    Do
    If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" Then
    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
    .To = cell.Value
    .Subject = "All Parts Order Confirmation Order # " & cell.Offset(0, 3).Value
    .HTMLBody = "All Parts has received your order." & "" & _
    "Date of Invoice: " & cell.Offset(0, -1) & "" & _
    "Customer Name: " & cell.Offset(0, 4) & "" & _
    "Address: " & cell.Offset(0, 2) & "" & _
    "Part ID: " & cell.Offset(0, 5) & "" & _
    "Part Description: " & cell.Offset(0, 6) & ""
    Set cell2 = cell.Offset(1, 0)
    Do While cell2 = cell
    .HTMLBody = .HTMLBody & _
    "Part ID: " & cell2.Offset(0, 5) & "" & _
    "Part Description: " & cell2.Offset(0, 6) & ""
    Set cell2 = cell2.Offset(1, 0)
    Loop
    .HTMLBody = .HTMLBody & "All-Parts Order#: " & cell.Offset(0, 3) & "" & _
    "John Doe" & "" & _
    "Parts Specialist" & "" & _
    "All Parts" & "" & _
    "800-ALL-PARTS" & "" & _
    "M-F (8:00AM - 5:00PM CDT)"
    .Display
    End With
    Set OutMail = Nothing
    End If
    Set cell = cell2
    Loop Until cell.Row > m
    Cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
    End Sub
    </code>
    See attached workbook.
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sending personalized email from Excel (2007)

    Sorry for the delay on the response. Your code worked perfectly. It's exactly what I was looking for.

    I've modified the htmlbody part a bit to get it to throw the information into a table, and I can get it to do it exactly as I need, however there are some very annoying white "borders" around each cell in the table. I've tried cellpadding and collapsing and I'm not getting the results I need. Been working on it for a few days and I'm about to pull my hair out.

    Here is what I have: (the code is pulling from additional cells other than what was shown in the example spreadsheet) And I'm also only posting the html part of the code.

    .HTMLBody = "All Parts Order Confirmation" & "" & _
    "Estimated Arrival Date" & cell.Offset(0, -1) & " (" & cell.Offset(0, 2) & " - " & cell.Offset(0, 3) & ")" & _
    "Customer Name " & cell.Offset(0, 9) & "" & _
    "<Thead><table border=0><tr><td>PartID </td><td> Part Description</td><td> Color" & "</td></tr></Thead>" & _
    "<tbody><tr><td style='border:solid windowtext 0pt; background:#99CCFF'>" & cell.Offset(0, 10) & "<td style='border:solid windowtext 0pt; background:#99CCFF'>" & _
    cell.Offset(0, 11) & "</td><td style='border:solid windowtext 0pt; background:#99CCFF'>" & _
    cell.Offset(0, 7) & "</tr></td>"
    Set cell2 = cell.Offset(1, 0)
    Do While cell2 = cell
    .HTMLBody = .HTMLBody & _
    "<tr><td style='border:solid windowtext 0pt; background:#99CCFF'n>" & cell2.Offset(0, 10) & "</td><td style='border:solid windowtext 0pt; background:#99CCFF'>" & _
    cell2.Offset(0, 11) & "</td><td style='border:solid windowtext 0pt; background:#99CCFF'>" & _
    cell2.Offset(0, 7)
    Set cell2 = cell2.Offset(1, 0)
    Loop
    .HTMLBody = .HTMLBody & "</tr></td></tbody></table>" & "All Parts Order#: " & cell.Offset(0, 6) & "" & _
    "John Doe" & _
    "Parts Specialist" & _
    "All Parts"


    I hope I've edited the above where it makes sense. I've had to remove some of the rows due to sensitive information. The main part I need help on is just the table part. What else do I need to do to get the border around the body of the table without having the white space between the cells? And keeping the header without any border at all?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sending personalized email from Excel (2007)

    That is not an Excel question any more, it's about HTML coding. You might ask a question about just the table part in the Web Design, Coding and Scripting forum.

Posting Permissions

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