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