Mailing to a mailing list

[jnaylor]

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

[Lewis]

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

[jnaylor]

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?????

[Lewis]

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

[stevenvanderkruk]

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