Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please please help!! Weird coding issue
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!! Mis match error 13 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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please please help!! Weird coding issue
Which line gives the error?
Note: You have declared Response as String. A better declaration would be Dim response As VBA.VbMsgBoxResult I am actually surprised that string worked... -- HTH... Jim Thomlinson "Neil Holden" wrote: 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!! Mis match error 13 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 | |||
Weird issue with very simple line chart - urgent | Excel Discussion (Misc queries) | |||
Is this weird or what? | Excel Worksheet Functions | |||
Weird Printing Issue | Excel Discussion (Misc queries) | |||
Excel VBA coding - Outlook and Shut down issue | Excel Worksheet Functions | |||
This is Weird - Dead Simple Issue with Sum Function | Excel Worksheet Functions |