File Browser for Outlook attachment on Access Form 3

[leslie746]

I need to put a dialog box - File Browser (would it be Activex Control, if so, which one?) for picking the file to be attached to Outlook e-mail sent from Access form.

The Outlook part works OK and my script so far looks like this:

Private Sub btnEmail_Click()
On Error GoTo Err_btnEmail_Click

Dim Email As String
Dim Subject As String
Dim notes As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail

.To = Me.Email
.Subject = Me.Subject
.Body = "Dear" & Me.Title & " " & Me.FirstName & " " & Me.Surname & Chr(10) & Me.notes
'.Send
.Display
End With

Me.EmailSent = vbYes
Me.lblSent.Visible = True
Me.lblSent.Caption = "E-mail successfully sent"

Exit Sub

Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_btnEmail_Click:
Exit Sub

Err_btnEmail_Click:
MsgBox Err.Description
Resume Exit_btnEmail_Click

objOutlook.Quit
Set objEmail = Nothing

End Sub

Can anybody assist, please?

 

[mp9]

Searching Access VBA help for FileDialog Object.

http://www22.brinkster.com/accessory/

 

[RoyVidar]

Or check out

http://www.mvps.org/access/api/api0001.htm

Roy-Vidar

 

[leslie746]

My thanks to both mp9 and RoyVidar.

On my form I have a textbox "Attachment" which gets filled with filename picked using the script below:

Private Sub cmdGetFile_Click()

Dim Attachment As String

Me.Attachment.Visible = True
Me.Attachment.SetFocus
Me.cmdGetFile.Visible = False
Me.lblSent.Visible = False

Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogFilePicker)

Dim vrtSelectedItem As Variant

With fd

If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
Attachment = vrtSelectedItem
Next vrtSelectedItem
Else
End If
End With

Me.Attachment = Attachment

End Sub

The above code enables to pick only one file as attachment and ignores multiple picks.

Can you please help in eradicating the problem.

Thanks.

 

[PHV]

With fd
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
Me.Attachments.Add vrtSelectedItem
Next vrtSelectedItem
End If
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RoyVidar]

A simple concatenation is probably what is needed for this visual thingie - but a couple of warnings

1 - do not name variables the same as objects on the form
2 - do rename all controls that you're manipulating in code, or reference in expressions.

[tt]dim strAttachment as string
...
strAttachment = strAttachment & "|" & vrtSelectedItem
...
Me!txtAttachment.Value = strAttachment[/tt]

But - you will probably get the same challenge when adding the attachements to the e-mail. Here's a go which does not take into consideration that there might be no attachements.

[tt]dim strAtt() as string
dim lngCount as long
strAtt = split(me!txtAttachment.value, "|")
for lngCount = 0 to ubound(strAtt)
.Attachments.Add strAtt(lngCount)
next lngCount[/tt]

- typed not tested

(btw - the colors you choose, makes it hard on these old eyes to read ...)

Roy-Vidar

 

[PHV]

Oops, sorry for typos :~/
With fd
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
Me!Attachment = Me!Attachment & " " & vrtSelectedItem
objEmail.Attachments.Add vrtSelectedItem
Next vrtSelectedItem
End If
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[leslie746]

Dear All,
Thank you very much for your kind assistance. You have made my day.

PHV - You helped me on number of previous occasions - special thanks.

RoyVidar - Point taken on choice of colours - thank you too!

Best Regards.