Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Mailing to a mailing list

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there a way of mailing an excel sheet to a mailing list using 'sendit', rather than listing the individual email addresses in arg1?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The following saves the active sheet as a new workbook in the temp directory. It takes info from a cell and inserts it into the filename. It then emails it as an attachment to a list held on another sheet, skipping blank lines, then kills the temp file.

    Sub Mail()

    myFile = "Problem_Report_" & Range("B5").Value
    ThisWorkbook.ActiveSheet.Copy
    ActiveWorkbook.SaveAs "C:Windowstemp" & myFile
    ThisWorkbook.Activate
    If Not IsNull(Application.MailSession) Then Application.MailLogon
    With ThisWorkbook.Worksheets("Sheet6").Cells
    For Each c In .Range("D2:D12").Cells
    s = c.Value
    If s = "" Then GoTo Skip
    Workbooks(myFile & ".xls").SendMail Recipients:=s, _
    Subject:="Here is an UPDATED Problem Report #" & Range("B5").Value, _
    ReturnReceipt:=False

    Skip:
    Next c
    End With

    Workbooks(myFile & ".xls").Close False
    Kill "C:Windowstemp" & myFile & ".xls"

    End Sub

    Modify to suit

    Good Luck

    Lewis

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks v much for your response, I have achieved the sending of the email already.
    My probll is specifying a mailing list (as set up in Outlook , eg: called 'mail-list') rather than typing all the seperate email addresses in when I have a big list of recipients. How can I get it to recognise the name of the mailing list?????

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This sounds more like you want to set up a group in your address book.

  5. #5
    New Member
    Join Date
    Jul 2007
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Please help!!

    Hi everyone,

    I am trying to put in this peace of code, but it keeps failing on me. I am not an expert, so the error might be everywhere.

    I tried the below, but for some reason, it keeps on crashing on the saving bit. Can someone please have a look at my code and tell me where it goes wrong?

    Thank you so much.

    Sub Mail()

    myFile = "Action log_" & Now()
    ThisWorkbook.ActiveSheet.Copy
    ActiveWorkbook.SaveAs Filename:="G:\Structureren ML_2" & myFile
    ThisWorkbook.Activate
    If Not IsNull(Application.MailSession) Then Application.MailLogon
    With ThisWorkbook.Worksheets("Dependencies").Cells
    For Each c In .Range("D3:D12").Cells
    s = c.Value
    If s = "" Then GoTo Skip
    Workbooks(myFile & ".xls").SendMail Recipients:=s, _
    Subject:="Here is an UPDATED Action log #" & Range("H1").Value, _
    ReturnReceipt:=False

    Skip:
    Next c
    End With

    Workbooks(myFile & ".xls").Close False
    Kill "C:Windowstemp" & myFile & ".xls"

    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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