Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 163
Default INPUT BOX in excel 2003

Below is the code, i've been told to use an input box but i'm clueless as to
how to do this, can you help me on this matter which would be greatly
appreciated.

Sub Macro()

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 Address" & 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 = Response
.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

'If Response = vbYes Then
' DefaultFolder = "M:\Contract\Current\Nationwide\Templates\Proj ect
'Brief&SOR\Project Briefs to be Approved prior to sending inc master SOR
'Project brief"
'If Right(DefaultFolder, 1) < "\" Then
' DefaultFolder = DefaultFolder & "\"
'End If
'DefaultFileName = Range("C7")
'If Right(UCase(DefaultFileName), 3) < "XLS" Then
' DefaultFileName = DefaultFileName & " " & _
' Format(Date, "dd-mm-yyyy") & ".xls"
' End If
'FileToSave = Application.GetSaveAsFilename _
'(DefaultFolder & DefaultFileName, filefilter:="Excel Files (*.xls)," _
' & "*.xls", Title:="Save File As...")
'If FileToSave = False Then
' Exit Sub
'Else
' ThisWorkbook.SaveAs _
' Filename:=FileToSave, _
' FileFormat:=ActiveWorkbook.FileFormat
'End If
'End If
End If
End Sub

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem when input Chinese in Excel 2003 Matthew[_3_] Excel Discussion (Misc queries) 0 September 10th 07 04:05 AM
Problem when input Chinese in Excel 2003 Matthew[_3_] Excel Discussion (Misc queries) 0 September 10th 07 03:45 AM
Excel 2003 in a WEB SERVICE - would like some input :) BillyD Excel Programming 2 June 29th 07 05:10 PM
Input mask on a form in Excel 2003 Lloyd Excel Programming 6 July 25th 06 03:07 PM
Limit text input in Excel 2003 Agent37 Excel Programming 2 November 15th 04 09:12 PM


All times are GMT +1. The time now is 06:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"