sending mail to distribution list in through Excel macro

[darronb]

I have now got to code working below to send an attactment with a subject line and body. Although it will only work with a single e-mail address. if i put in the name ofa Distribution list it does not work.

Can anyone give me any pointers how to sent the mail to a Distribution list.

Sub SendNotif()

Dim bStarted As Boolean
Dim oOutlookApp As Object
Dim oItem As Object

On Error Resume Next

'Get Outlook if it's running
Set oOutlookApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
'Outlook wasn't running, start it from code
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If

'Create a new mailitem
Set oItem = oOutlookApp.CreateItem(0)

With oItem
'Set the recipient for the new email
.To = "another@abc.com"
.attachments.Add ActiveWorkbook.FullName
'Set the subject
.Subject = "Progress Report W/E (Week)"
'Set the message body
.Body = "Please find attached the relog report for w/e 08/12/06. The relogs are now in a new format, all data is contained in one Excel workbook to navigate between different reports." & Chr(10) _
& "Use the work sheet tabs at bottom of the page." & Chr(10) _
& "If you find any problems with this report please email Darron Broadhurst or Chris Harvey with a description of the problem." & Chr(10) _
& "Regards" & Chr(10) _
& "Janine"
.Send
End With

If bStarted Then
'If we started Outlook from code, then close it
oOutlookApp.Quit
End If

'Clean up
Set oItem = Nothing
Set oOutlookApp = Nothing

End Sub

 

[RonRepp]

This is the e-mail procudure that I've been using for years. I get multiple names through a text file, but I co not know how to access Distribution Lists. It was always my impression that as long as you typed the name of the DL that it would populate properly, but I've never tried.

Public Sub SendEMail()

    Dim strIniPath, strLine As String
    Dim FSO, txtFile1
        
    Set myOlApp = CreateObject("Outlook.Application")
    Set myitem = myOlApp.CreateItem(olMailItem)
        myitem.Body = vbNewLine & vbNewLine & _
            vbNewLine & vbNewLine & "Here are your files for " & eDate
    Set myAttachments = myitem.Attachments

'''Send the attachment
    myAttachments.Add App.Path & "\history\" & FName & ".xls", _
        olByValue, 1, FName
    
    myitem.Subject = "Subject"
    
    [b]Set FSO = CreateObject("Scripting.FileSystemObject")
    strIniPath = App.Path & "\Address.txt"
    If FSO.fileexists(strIniPath) Then
        Set txtFile1 = FSO.OpenTextFile(strIniPath, 1, False)
        On Error GoTo ExitErr
        
        Do While Not txtFile1.AtEndOfStream
        
            [i]strLine = txtFile1.readline
            myitem.Recipients.Add (strLine)[/i]
            
        Loop
        
    End If
ExitErr:
    txtFile1.Close[/b]
    myitem.Send
        
End Sub

I hope this helps.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.