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

    Send Mail (Complicated) (XP)

    I've attached a spreadsheet with an example of the data I receive.

    What I need to do is:
    Create a new spreadsheet for each separate name.
    List the data for that name only within each named spreadsheet.
    Send an individual email containing only the data within each individual spreadsheet. Repeat for each remaining one.
    Within the email, I'd like the subject to state the name of the person within it, along with some additional text (which will be the same for each individual)

    Email Info:

    To: JohnDoe@email.com
    Subject John Doe: Action Needed

    Message Body: John Doe, you have 5 items that need immediate action.
    The ID Numbers are:
    1
    7
    9
    10
    16


    Any help is much appreciated.
    Attached Files Attached Files

  2. #2
    New Lounger
    Join Date
    Feb 2003
    Location
    Long Beach, California, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Send Mail (Complicated) (XP)

    This macro will do something close to what you wanted. I put the entire message in the Subject area because there is no message argument to the SendMail method.

    Sub Macro1()
    Dim cell, cell2 As Range
    Dim wb As Workbook
    Dim src, dst As Worksheet
    Dim txtSubject As String
    Set src = ThisWorkbook.Sheets(1)
    src.[E1] = "Name"
    src.[E4] = "Name"
    src.[G1] = "ID"
    src.[H1] = "Name"
    src.[I1] = "Email"
    src.[A1].CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=[E4], Unique:=True

    For Each cell In src.[E4].CurrentRegion.Cells
    If cell.Row > 4 Then
    src.[E2] = cell
    src.[A1].CurrentRegion.AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=src.Range("E1:E2"), _
    CopyToRange:=src.Range("G1:I1"), _
    Unique:=False
    src.[G1].CurrentRegion.Copy
    Set wb = Workbooks.Add
    Set dst = wb.Sheets(1)
    dst.Paste
    dst.Columns.AutoFit
    dst.[A1].Select
    txtSubject = "Action needed: You have "
    txtSubject = txtSubject & dst.[A1].CurrentRegion.Rows.Count - 1
    txtSubject = txtSubject & " items that need immediate action."
    txtSubject = txtSubject & " The ID Numbers are: "
    For Each cell2 In dst.[A1].CurrentRegion.Columns(1).Cells
    If cell2.Row > 1 Then
    txtSubject = txtSubject & cell2 & ", "
    End If
    Next cell2
    txtSubject = Left(txtSubject, Len(txtSubject) - 2)
    wb.SendMail _
    Recipients:=dst.[C2], _
    Subject:=txtSubject
    ActiveWindow.Close SaveChanges:=False
    End If
    Next cell
    Application.CutCopyMode = False
    src.[A1].CurrentRegion.Offset(0, 4).Resize(, 5).ClearContents
    End Sub
    Attached Files Attached Files

  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: Send Mail (Complicated) (XP)

    Hey thanks. This works great.

    Couple of questions about it...

    1. Is there a way to send it from an account other than the default account?

    2. A "warning" pops up for each email sent stating that Excel is trying to send an email and it might be a virus. "Yes" must be clicked after a 5 second wait. Any way to bypass that?

    Thanks again.

  4. #4
    New Lounger
    Join Date
    Feb 2003
    Location
    Long Beach, California, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Send Mail (Complicated) (XP)

    1. I don't know of any way using the SendMail method. I imagine you could write some code to change the default account. You might also be able to use some SendKeys commands to open a different account.
    2. It doesn't do that on mine. It may have to do with the Macro Virus Protection setting. You can change that on the TOOLS*OPTIONS*GENERAL tab.

  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: Send Mail (Complicated) (XP)

    Does anyone know how to change the sender from the default account to an alternate account?

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

    Re: Send Mail (Complicated) (XP)

    Bumping this back up from a few months ago...


    I need this to perform the same function, but on a grander scale.
    The sheet I pull from contains 30 columns and over 500 rows.
    The email would need to go to each person listed (possibly over 100) individually.

    I've messed around with the code to try to adjust, but I keep receiving errors...I'm just not familiar enough with it.

    Any suggestions?

    Thanks in advance.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Send Mail (Complicated) (XP)

    Does this site help. He uses sendmail in many different applications. It even includes a sendmail addin.

    http://www.rondebruin.nl/sendmail.htm

    Steve

Posting Permissions

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