Distribution Lists

[mike_wilds]

Hi

I am looking for help to help me build a distribution list from a list of email addresses in another excel spreadsheet. I am aware this probably has already been posted, but I have tried looking for the code on here all day and still unable to find one. I really need this now asap.

I am putting together several excel reports (all different files) and I would like to refer to another file which has a distribution list of clients I need to email the reports to. This is in a file called Distribution Lists and has several lists, each one relating to a different report. The reason this is in another file is because my colleagues all need access to the same distribution list so this is on a shared drive.

I need the macro code to take all the email addresses from cells B3 to the end of the list in that column and put this into outlook.

I have already produced much of the code already and this will be the last thing i need to make this perfect.

here is the code

Sub Email()
Dim text_date As String
Dim strto As String
Dim strcc As String
Dim strbcc As String
Dim strsub As String
Dim strbody As String
Dim SigString As String
Dim Signiture As String
Dim Distribution As String
Dim Recipients As String, c As Range
 
Dim sh As Worksheet
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
 
today = Weekday(Now())
If today = 2 Then
text_date = "Friday"
Else
If today = 3 Then
text_date = "Monday"
Else
If today = 4 Then
text_date = "Tuesday"
Else
If today = 5 Then
text_date = "Wednesday"
Else
If today = 6 Then
text_date = "Thursday"
Else
text_date = "P"
End If
End If
End If
End If
End If
'works out the last working day
SigString = "C:\Documents and Settings\" & Environ("username") & "\Application Data\Microsoft\Signatures\Avaya.txt" 'takes the relevaant advisors signature
If Dir(SigString) <> "" Then
Signature = GetSignature(SigString)
Else
Signature = ""
End If
'takes the signature of the person emailing the information
 
If today = 2 Then
Title = "Avaya Attendance Agent" & " " & Format((Date) - 3, "mmmm yyyy") 'if today is monday, take 3 days off
Else
Title = "Avaya Attendance Agent" & " " & Format((Date) - 1, "mmmm yyyy") 'if today is not monday, take 1 day off
End If
'changes the title to the relevant date as we only work monday to Fridays
 
 
strsub = "Avaya Attendance"
strbody = "Good Morning" & vbLf & vbLf & _
"Please see attached" & " " & text_date & " " & "stats summarised by agents." & vbLf & vbLf & _
"Thanks" & vbLf & vbLf & Signature
'starts the body of the text.
 
With OutMail
.To = "mike.wilds@mike-wilds.com" 'not a real email address
.Subject = Title
.Attachments.Add ActiveWorkbook.FullName
.body = strbody
.Display
End With
'emails the attachment along with the text body and signature
 
End Sub

If you could help me refer to another spreadsheet to take the relevant distribution list, this would be great.

Thanks

 

[JP2112]

Instead of "all the email addresses from cells B3 to the end of the list in that column" can you use a dynamic range name to make it easier to select the correct list?