I have done up a method (that works without considering the weeknumber) to send out emails to a list of addresses through outlook. See Below:

[VBA]
Sub SendWithAtt()
Dim olApp As Outlook.Application
Dim olMail As MailItem

Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)


ActiveWorkbook.Save

Dim n As Integer
Dim Recip As String
n = 2
Recip = ""
Do Until Cells(n, 4) = ""
Recip = Recip & ";" & Cells(n, 4)
n = n + 1
Loop

With olMail
.BCC = Recip
.Subject = "BagAge Promotions and News!"
.Display
End With
Set olMail = Nothing
Set olApp = Nothing


End Sub
[/VBA]


Okie, right now. i am trying to create another method that looks through the weeknumber in the excel sheet and obtain only those addresses that matches the weeknumber of say today. But i am having problem trying to get the addresses into the mail window(it pops up but does not fill up the BCC list). The email addresses are filled in the column before the weeknumber, thats why i did an activeoffset(0,-1)..

If anyone knows this please help me! Thanks. See below

[VBA]
Sub SendSpecial()

Dim c As Range, Week As Integer, Recip As String
Dim today As Integer, email As String

today = Range("G1")

For Each c In Range("weeknumber")
Range("c").Select
If c = today Then
ActiveCell.Offset(0, -1).Range("A1").Select
email = Selection.Text
Recip = email & ";" & Recip
End If
Next c

Dim olApp As Outlook.Application
Dim olMail As MailItem
Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)

With olMail
.BCC = Recip
.Subject = "Birthday Greetings from BagAge!"
.Display
End With
Set olMail = Nothing
Set olApp = Nothing

End Sub
[/VBA]