Macro that will prompt for file attachemnt

[romyjames]

Hi,
I have a command button on a excel sheet. When i press this button, It copies and pastes the excel sheet onto outlook. However before i send the mail. I want teh code to prompt the user for an attachment. When it does this i want it to open a Insert file dialog box, so user can select the file to attach. Been looking for a while.
My complete code is as follows:-

This is a working code:-- I want to add the attachment propmt to this if possible. the attachement Prompt should appear after this worksheet is pasted in outlook.

Sub SendMailPinpad()
'Copy excell and send to outlook'
Worksheets("Pinpad Order").Activate
Application.ScreenUpdating = False
Dim OutApp As Object
Dim OutMail As Object
Dim FSO As Object
Dim HTMLcode As String
Dim HTMLfile As Object
Dim MyApp As Boolean
Dim olApp As Object
Dim Rng As Range
Dim TempFile As String
Dim Wks As Worksheet


Const ForReading As Long = 1
Const olMailItem = 0
Const olFormatHTML = 2
Const UseDefault As Long = -2

Sheets("Pinpad Order").Visible = True

If IsMissing("A1:B26") Then
Set Rng = Selection
Else
Select Case TypeName("A1:B26")
Case Is = "Range"
Set Rng = Range_To_Send
Case Is = "String"
Set Rng = Evaluate("A1:B26")
Case Else
MsgBox "Your Selection is Not a Valid Range."
GoTo Cleanup
End Select
End If
Set Wks = Rng.Parent
TempFile = Environ("Temp") & "\Email.htm"
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
'Convert the Message worksheet into HTML
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
FileName:=TempFile, _
Sheet:=Wks.Name, _
Source:=Rng.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

'Read the HTML file back as a string
Set FSO = CreateObject("Scripting.FileSystemObject")
Set HTMLfile = FSO.GetFile(TempFile).OpenAsTextStream(ForReading, UseDefault)
HTMLcode = HTMLfile.ReadAll
HTMLfile.Close

'Clean up the HTML code
HTMLcode = Replace(HTMLcode, "align=LEFT x:publishsource=", _
"align=LEFT x:publishsource=")
Set OutMail = OutApp.CreateItem(olMailItem)
Sheets("Pinpad order").Visible = False
On Error Resume Next
With OutMail
.from = ""
.To = ""
.CC = ""
.BCC = ""
.Subject = ""
.BodyFormat = olFormatHTML
.HTMLBody = HTMLcode
.Display
End With
Cleanup:
'Delete the Temp File
If Dir(TempFile) <> "" Then Kill TempFile
'Delete the Publish Object
With ActiveWorkbook.PublishObjects
If .Count <> 0 Then .Item(.Count).Delete
End With
'Free memory resources
Set olApp = Nothing
Set olEmail = Nothing
Set FSO = Nothing
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

[mudraker]

Welcome to the Forum..

Please read forum rules & add Code Tags to your post