Prev: dcount won't work?
Next: trying to ensure that Access closes down
From: Gary B on 19 Feb 2010 01:34
Hi there,
I have a form, with a subform.
The subform has an "attachment" field.
This subform is displayed in datasheet format on the main form.

I have code on the main form ,attached to a cmdButton, that sends an email.

I now wish to amend that code so that the attachment is attached to the
email - if there is one.

Dim App As Object
Dim Itm As Object
Dim strAttachmentname

Set App = CreateObject("Outlook.Application")

' all str variables defined and represent values on the main form
' get the attachment name if there is one ????? (this is what I need)
' what if there is more than one record on the subform and there are
different attachments ? I need them all :-)

Set Itm = App.CreateItem(0)
With Itm
.Subject = strSubject
.To = strSendto
.CC = strCCTo
.Body = strBodyTxt
.Attach(strattachmentname) ???
.Display (so user can amend email before sending)
End With

From: Mark Andrews on 19 Feb 2010 10:27
Here's some code I use:
'Open recordset of attachments, save each one to disk and assign to
attachments() array
For iii = 1 To 9
Attachments(iii) = ""
Next iii
filepath = CurrentDBDir() & "Attachments999\"
If Len(Dir(filepath, vbDirectory)) = 0 Then
MkDir filepath
End If
iii = 1
Set RSAttachments = rs.Fields("Attachments").Value
While Not RSAttachments.EOF
If (FileExists(filepath &
RSAttachments.Fields("FileName").Value)) Then
Kill filepath & RSAttachments.Fields("FileName").Value
End If
RSAttachments.Fields("FileData").SaveToFile filepath
Attachments(iii) = filepath &
RSAttachments.Fields("FileName").Value
iii = iii + 1
RSAttachments.MoveNext
Wend
RSAttachments.Close


Function CurrentDBDir() As String

Dim strDB As String, strCurrentDir As String

strDB = CurrentDb.Name
strCurrentDir = Left(strDB, Len(strDB) - Len(Dir(strDB)))

CurrentDBDir = strCurrentDir
End Function


It saves all the attachments (well, up to 9) that are in the attachments
field in directory attachments999 and populates the attachments() array with
filenames to be used when building the email.

Hope this makes sense and helps you out,
Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com


"Gary B" <GaryB(a)discussions.microsoft.com> wrote in message
news:B3D118C2-E3CF-49BF-B9A5-AE1FC2A5FAB3(a)microsoft.com...
> Hi there,
> I have a form, with a subform.
> The subform has an "attachment" field.
> This subform is displayed in datasheet format on the main form.
>
> I have code on the main form ,attached to a cmdButton, that sends an
> email.
>
> I now wish to amend that code so that the attachment is attached to the
> email - if there is one.
>
> Dim App As Object
> Dim Itm As Object
> Dim strAttachmentname
>
> Set App = CreateObject("Outlook.Application")
>
> ' all str variables defined and represent values on the main form
> ' get the attachment name if there is one ????? (this is what I need)
> ' what if there is more than one record on the subform and there are
> different attachments ? I need them all :-)
>
> Set Itm = App.CreateItem(0)
> With Itm
> .Subject = strSubject
> .To = strSendto
> .CC = strCCTo
> .Body = strBodyTxt
> .Attach(strattachmentname) ???
> .Display (so user can amend email before sending)
> End With
>
 | 
Pages: 1
Prev: dcount won't work?
Next: trying to ensure that Access closes down