Linking Excel With Vba Please Help.

[countryfan_nt]

Friends, I really need help on this. I have a VBA code, that enables me
to send cetain excel sheets by email to certain people.
everything seems OK and working except that I want Wedensday to be
changeable from the excel sheet.

Here is the whole code. I truly appreciate your help. oh, and I plan to
have the changeable date placed in cell bq11, sheet name is: HOME.

Thanks agian.
Nawaf

code:-------------
Sub DIV1()

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
Sheets(Array("AL-Mulhim", "Al-Sane", "Al-Sudairy", "Osama", "ADJ REV",
"ACTIVE DEALS", "ACTIVE DEALS CHART", "CLSD DEALS", "CLOSED DEALS
CHART", "HOME", "Report")).Copy
Set wb = ActiveWorkbook
With wb
SaveAs "DIV. 1 - PIPELINE UPDATE" & ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
To = "1div"
CC = "DIV1 HEAD"
BCC = ""
Subject = "DIV. 1 PIPELINE UPDATING" & " " & Date

strHTML = "

Gentlemen,

* Please update your deals then click the top button to send it by
email.
"
strHTML = strHTML & "
* Please insert the product group (in column E).
"
strHTML = strHTML & "
* I would appreciate it if you would have it ready before 1 PM on
Wednesday (I will not be able to receive any emails after 1)."
strHTML = strHTML & "
  As I will have it finalized & distributed to Both
Khalid & the Division Heads on the same day.
"
strHTML = strHTML & "
* The update will be discussed on Sunday's Pipeline meeting 9:00.
"
strHTML = strHTML & "
* Please do not reply to this message if there are no UPDATES.
"
strHTML = strHTML & "
* The probability (%) is categorized as follows:"

strHTML = strHTML & "
     0%: Deals discussed internally And/Or
with customer.
"
strHTML = strHTML & "
   25%: Deals where marketing proposals were made.
"
strHTML = strHTML & "
   50%: Deals where we have been mandated in writing.
"
strHTML = strHTML & "
   75%: Deals which have been signed.
"
strHTML = strHTML & "
 100%: DEAL DONE.
"

strHTML = strHTML & "
All the best,

"
strHTML = strHTML & "
Nawaf,
"
strHTML = strHTML & "@ 7572
"
strHTML = strHTML & ""

HTMLBody = strHTML



Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
Display 'or .Display
End With
ChangeFileAccess xlReadOnly
Kill .FullName
Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[\(:][OZDOC1050][:\)]

Hi Nawaf,
I have not tried this but it should be close, you could place
a dial up in range A1 ( or wherever ) with the days of the week in it then
dial up the day required. ( you could use a separate sheet but would need to
reference it )

regards
Pete

strHTML = strHTML & "

* I would appreciate it if you would have it ready before 1 PM on"
& range("a1").value & "(I will not be able to receive any emails after 1)."
strHTML = strHTML & "
  As I will have it finalized & distributed to Both
Khalid & the Division Heads on the same day.