Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking Excel With Vba Please Help.
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 -- countryfan_nt ------------------------------------------------------------------------ countryfan_nt's Profile: http://www.excelforum.com/member.php...o&userid=11051 View this thread: http://www.excelforum.com/showthread...hreadid=345779 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Linking Excel With Vba Please Help.
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. "countryfan_nt" wrote in message ... 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 -- countryfan_nt ------------------------------------------------------------------------ countryfan_nt's Profile: http://www.excelforum.com/member.php...o&userid=11051 View this thread: http://www.excelforum.com/showthread...hreadid=345779 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel MSP linking | Excel Discussion (Misc queries) | |||
Object linking/Worsksheet linking/etc... | Excel Discussion (Misc queries) | |||
An error msg in Excel 2007 regarding Linking but no linking in fil | Excel Discussion (Misc queries) | |||
Linking in Excel | Excel Discussion (Misc queries) | |||
data entry in excel by linking to other word doc. or excel sheets | Excel Worksheet Functions |