Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro that uses data on diff. worksheet
hello I have the code below which orginally was being used only to work off the current sheet. Now I have decided to put the macro button a different worksheet. How do I set the the code so it uses the data on "Datasheet"? ie. for emails and msgs?? Many thanks!! Private Declare Function ShellExecute Lib "shell32.dll" _ Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _ ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _ ByVal nShowCmd As Long) As Long Code: -------------------- Sub SendEMail() Dim Email As String, Subj As String Dim Msg As String, URL As String Dim r As Integer, x As Double Dim cell As Range For r = 7 To 8 'data in rows 2-4 ' Get the email address Email = Cells(r, 2) ' Message subject Subj = "Recruitment Activity Statement " ' Compose the message Msg = vbCrLf Msg = Msg & "Dear " & Cells(r, 3) & vbCrLf & vbCrLf Msg = Msg & "Total Executive Interviews to date: " & Cells(r, 17) & vbCrLf & vbCrLf Msg = Msg & "Your target for FY06: " & Sheets("Sheet1").Range("B1").Value & vbCrLf & vbCrLf Msg = Msg & "Remaining to hit target: " & Cells(r, 21) & vbCrLf & vbCrLf Msg = Msg & "In order to achieve this you need to conduct " Msg = Msg & Cells(r, 22) & " interviews each month." & vbCrLf & vbCrLf Msg = Msg & "Your current Executive Interviewer rank: " Msg = Msg & Cells(r, 21) & vbCrLf & vbCrLf Msg = Msg & "Msg from recruitment team - " & Cells(r, 1) & vbCrLf & vbCrLf & vbCrLf Msg = Msg & "Thanks for your continued involvement! " & vbCrLf & vbCrLf Msg = Msg & "The UKDC Recruitment Team" ' Replace spaces with %20 (hex) Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20") Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20") ' Replace carriage returns with %0D%0A (hex) Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A") ' Create the URL URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg ' Execute the URL (start the email client) ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus ' Wait two seconds before sending keystrokes Application.Wait (Now + TimeValue("0:00:02")) ' Application.SendKeys "%s" Next r End Sub -------------------- -- periro16 ------------------------------------------------------------------------ periro16's Profile: http://www.excelforum.com/member.php...o&userid=26345 View this thread: http://www.excelforum.com/showthread...hreadid=396763 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro that uses data on diff. worksheet
Put the code in a standard module, not a sheet or workbook module. Replace
Cells(r, with Sheets("Datasheet").Cells(r, and call or assign the macro from the button, depending what kind of button it is. HTH. Best wishes Harald "periro16" skrev i melding ... hello I have the code below which orginally was being used only to work off the current sheet. Now I have decided to put the macro button a different worksheet. How do I set the the code so it uses the data on "Datasheet"? ie. for emails and msgs?? Many thanks!! Private Declare Function ShellExecute Lib "shell32.dll" _ Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _ ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _ ByVal nShowCmd As Long) As Long Code: -------------------- Sub SendEMail() Dim Email As String, Subj As String Dim Msg As String, URL As String Dim r As Integer, x As Double Dim cell As Range For r = 7 To 8 'data in rows 2-4 ' Get the email address Email = Cells(r, 2) ' Message subject Subj = "Recruitment Activity Statement " ' Compose the message Msg = vbCrLf Msg = Msg & "Dear " & Cells(r, 3) & vbCrLf & vbCrLf Msg = Msg & "Total Executive Interviews to date: " & Cells(r, 17) & vbCrLf & vbCrLf Msg = Msg & "Your target for FY06: " & Sheets("Sheet1").Range("B1").Value & vbCrLf & vbCrLf Msg = Msg & "Remaining to hit target: " & Cells(r, 21) & vbCrLf & vbCrLf Msg = Msg & "In order to achieve this you need to conduct " Msg = Msg & Cells(r, 22) & " interviews each month." & vbCrLf & vbCrLf Msg = Msg & "Your current Executive Interviewer rank: " Msg = Msg & Cells(r, 21) & vbCrLf & vbCrLf Msg = Msg & "Msg from recruitment team - " & Cells(r, 1) & vbCrLf & vbCrLf & vbCrLf Msg = Msg & "Thanks for your continued involvement! " & vbCrLf & vbCrLf Msg = Msg & "The UKDC Recruitment Team" ' Replace spaces with %20 (hex) Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20") Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20") ' Replace carriage returns with %0D%0A (hex) Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A") ' Create the URL URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg ' Execute the URL (start the email client) ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus ' Wait two seconds before sending keystrokes Application.Wait (Now + TimeValue("0:00:02")) ' Application.SendKeys "%s" Next r End Sub -------------------- -- periro16 ------------------------------------------------------------------------ periro16's Profile: http://www.excelforum.com/member.php...o&userid=26345 View this thread: http://www.excelforum.com/showthread...hreadid=396763 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
plotting column chart whch colors the bars diff for diff comm | Charts and Charting in Excel | |||
SUMIF formula required to search for 2 diff values in 2 diff colum | Excel Worksheet Functions | |||
how you make links between diff. cells on diff. work sheets | Excel Worksheet Functions | |||
copy range on every worksheet (diff names) to a master worksheet (to be created) | Excel Programming | |||
copy range on every worksheet (diff names) to a master worksheet (to be created) | Excel Programming |