Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 - button to email selected users.
Hi, I have a button that when pressed asked to select a worksheet and select
email addresses, when the email addresses have been selected it will then email that particular user. Columns A,B and C needs to be hidden but when I hide these columns I get a mismatch error, if the columns stay visable it works? please help this is very fustrating!! Sub MAIL_PIP() Dim Response As String Dim DefaultFolder As String, DefaultFileName As String Dim FileToSave Dim OutApp As Object 'this emails operations manager Dim OutMail As Object Dim strbody As String Response = MsgBox("Are you sure you want to save the PIP report?", _ vbYesNo + vbInformation + vbDefaultButton2) If Response = vbYes Then strbody = "PIP" & " for " & Sheets("PIP").Range("A13").Value & " " & _ Sheets("PIP").Range("B13").Value & " " & "Ready For Review" Set EmailAddr = Application.InputBox("Select Email Addresses, Click on the Email Worksheet" & vbCrLf & _ "Hold down Contrl Key to select multiple addresses", Type:=8) Destination = "" For Each cell In EmailAddr If Destination = "" Then Destination = cell Else Destination = Destination & ";" & cell End If Next cell ActiveWorkbook.Save Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) strbody = "PIP" & " for " & Sheets("PIP").Range("A13").Value & " " & _ Sheets("PIP").Range("B13").Value & " " & "Ready For Review" On Error Resume Next With OutMail ..To = Destination ..CC = "" ..BCC = "" ..Subject = "PIP Ready For Review" ..Body = strbody ..Send 'or use .Display End With On Error GoTo 0 Set OutMail = Nothing Set OutApp = Nothing Application.DisplayAlerts = True Application.ScreenUpdating = True End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protection for selected users | Excel Discussion (Misc queries) | |||
Excel 2007 time schedule for non Excel users to fill in and email | Excel Discussion (Misc queries) | |||
MS Excel email button | Excel Discussion (Misc queries) | |||
How do I lock a radio button group if a N/A button is selected | Excel Discussion (Misc queries) | |||
How do I email selected excel cells to multiple recipients? | Excel Worksheet Functions |