Send stored attachment via email

[perlfan]

Hi!!

I am working with Access 2007. In my application users can store files in the database. For that I am using the attachment - datatype.

When a user sends an email from Access through Outlook I would like to attach this file (if there is one stored in that record).

Sending and everything works fine, but how do I select the attachment to get it into the email?

This is the attachment part in my email code:

 If Not IsMissing(xxxx) Then
                Set objOutlookAttach = .Attachments.Add(xxxx)
   End If

Thank you in advance - Frank

 

[perlfan]

Hi!

I did now manage to select e.g. the filename from the attached file in the table with the following code:

Public Sub AttachmentsAuslesenII()
Dim db As DAO.Database
Dim rst As Recordset2
Set db = CurrentDb

Set rst = db.OpenRecordset("SELECT ID, text.FileData, " _
& "text.Filename,text.FileURL FROM Translations", dbOpenDynaset)
Do While Not rst.EOF
Debug.Print rst!ID, rst("text..Filename")
Debug.Print rst!ID, rst("text..FileData")
rst.MoveNext
Loop
rst.Close

End Sub

Does anyone have an idea how I attacht this file (filename, filedata) to my email? Thank you in advance

Frank

 

[NigelShaw]

Hi

I do but not right now as I'm on my phone!!

I have a routine that will get the path of the attachment and send the file.

I'll post it in a bit


Nigel

 

[perlfan]

....still on the phone??

Would be nice if you could share that piece of code with me.

thx, frank

 

[NigelShaw]

Hi

on my phone as in Internet on my iPhone with no pc available. I'll sort it out when I get to work

nigel

 

[NigelShaw]

Hi

This should work ( well, it does with me )

make this variable public either in a public module or at the top of your open module under "Option Explicit"

Public strFullAttachmentPath as String

this routine will collect the current attachment path and store it in a string
( i would recommend a query to filter the attachment you want to send )

Dim fldAttach As DAO.Field2
Dim strFilePath As String
Dim strTempDir As String
Dim db As DAO.Database
Dim rst As Recordset
Dim rstAtt As DAO.Recordset2
Dim strFieldName As String
Dim strFullAttachmentPath As String
    
'set up your fixed variables here 
strTempDir = Environ("Temp")

'set up a recordset to your table or query
Set db = CurrentDb
Set rst = db.OpenRecordset("Translations")

'set up the field that holds the attachment
strFieldName = rst!YourAttachmentFieldHere
 
Set rstAtt = rst.Fields(strFieldName).Value
    

If Right(strTempDir, 1) <> "\" Then strTempDir = strTempDir & "\"
strFilePath = strTempDir & rstChild.Fields("FileName").Value
    
'set the public variable for later use in your email routine or other use.
'this is overwritten everytime this routine is used.

strFullAttachmentPath = strFilePath 'this holds the path of the attachment
    
'i used a msgbox to check the path but you can remove it
MsgBox (strFullAttachmentPath)
    
'close your recordset
rst.Close
db.Close

'clean up
Set rst = Nothing
Set db = Nothing

add this to your email settings

.Attachments.Add(strFullAttachmentPath)

and the file will be emailed from its location.

how are you sending the email? is it through Outlook via VBA?

let me know how you get on



Nigel

 

[Joy]

Hi I'm trying to use this code in Access 2007 but continue to get a user-defined error.

I've modified the code - not sure if I did it right:

Private Sub Command74_Click()

Dim fldAttach As DAO.Field2
Dim strFilePath As String
Dim strTempDir As String
Dim db As DAO.Database
Dim rst As Recordset
Dim rstAtt As DAO.Recordset2
Dim strFieldName As String
Dim strFullAttachmentPath As String

'set up your fixed variables here
strTempDir = Environ("Temp")

'set up a recordset to your table or query
Set db = CurrentDb
Set rst = db.OpenRecordset("CONTRACTS")

'set up the field that holds the attachment
strFieldName = rst!EXECUTED_CONTRACT

Set rstAtt = rst.Fields(strFieldName).Value


If Right(strTempDir, 1) <> "\" Then strTempDir = strTempDir & "\"
strFilePath = strTempDir & rstChild.Fields("FileName").Value

'set the public variable for later use in your email routine or other use.
'this is overwritten everytime this routine is used.

'this holds the path of the attachment

'i used a msgbox to check the path but you can remove it
MsgBox (strFullAttachmentPath)

'**************************************************************
'*create string with email address

'strEmail = [VENDOR CONTACT EMAIL]
strEmail = [VENDOR CONTACT EMAIL]
strFullAttachmentPath = strFilePath


'close your recordset
rst.Close
db.Close

strBody = strBody & "Dear " & [VENDOR CONTACT NAME] & ":" & Chr(13) & Chr(13)
strBody = strBody & "Attached is the unsigned contract. Please sign & return the contract as soon as possible, it is required to execute a Purchase Order." & Chr(13) & Chr(13)

strBody = strBody & "Contract ID: " & [Contract ID] & Chr(13)
strBody = strBody & "Contract Type: " & [CONTRACT TYPE] & Chr(13)
strBody = strBody & "Contract Start: " & [CONTRACT START (EFFECTIVE DATE)] & Chr(13)
strBody = strBody & "Contract End: " & [CONTRACT END (TERMINATION) DATE] & Chr(13)
strBody = strBody & "Purchase Type: " & [CONTRACT REQUEST TYPE] & Chr(13)
strBody = strBody & "Amount: " & AMOUNT & Chr(13) & Chr(13)
strBody = strBody & "Please refernce the above Contract ID number when inquiring about this contra" & Chr(13) & Chr(13)
strBody = strBody & "Best Regards," & Chr(13) & Chr(13)
strBody = strBody & "Lazara Stinnette, USF Purchasing"




With MailOutLook
.To = [VENDOR CONTACT EMAIL]
.subject = "Contract Info"
.Body = strBody
.Attachments.Add (strFullAttachmentPath)
.Display

'clean up
Set rst = Nothing
Set db = Nothing

End Sub


Please help - I am desperate

 

[NigelShaw]

Hi,

exactly what did you change? can you highlight the changes? you do it by highlighting the text you want highlighted and then you click the big blue A and select a colour.

to separate your code from your message, highlight your code and then click the # hash button and this will put the code in a code wrap.

then i should be able to see what has happened



Nidge

 

[Joy]

Hi,

I am very new to databases - only built this one database to helpout the office - got in over my head...

A few things:

• My changes are in purple as I didn't truly change your code.
• Should any particular VBA references be checked?
• I don't know the actual file locations - though I suspect it's a Temp file.

Private Sub Command74_Click()
 
 Dim fldAttach As DAO.Field2
 Dim strFilePath As String
 Dim strTempDir As String
 Dim db As DAO.Database
 Dim rst As Recordset
 Dim rstAtt As DAO.Recordset2
 Dim strFieldName As String
 Dim strFullAttachmentPath As String
     
 'set up your fixed variables here
 strTempDir = Environ("Temp")
 
 'set up a recordset to your table or query
 Set db = CurrentDb
 Set rst = db.OpenRecordset("[COLOR=Indigo]CONTRACTS[/COLOR]")
 
 'set up the field that holds the attachment
 strFieldName = rst![COLOR=Indigo]EXECUTED_CONTRACT[/COLOR]
  
 Set rstAtt = rst.Fields(strFieldName).Value
     
 
 If Right(strTempDir, 1) <> "\" Then strTempDir = strTempDir & "\"
 strFilePath = strTempDir & rstChild.Fields("FileName").Value
     
 'set the public variable for later use in your email routine or other use.
 'this is overwritten everytime this routine is used.
 
  'this holds the path of the attachment
     
 'i used a msgbox to check the path but you can remove it
 MsgBox (strFullAttachmentPath)
 
 '*************************************************  *************
 '*create string with email address
 
 [COLOR=Indigo]'strEmail = [VENDOR CONTACT EMAIL]
 strEmail = [VENDOR CONTACT EMAIL][/COLOR]
 strFullAttachmentPath = strFilePath
 
 'close your recordset
 rst.Close
 db.Close
  
[COLOR=Indigo]strBody = strBody & "Dear " & [VENDOR CONTACT NAME] & ":" & Chr(13) & Chr(13) 
strBody = strBody & "Attached is the unsigned contract. Please sign  & return the contract as soon as possible, it is required to execute  a Purchase Order." & Chr(13) & Chr(13)
  
 strBody = strBody & "Contract ID: " & [Contract ID] & Chr(13)                 
 strBody = strBody & "Contract Type: " & [CONTRACT TYPE] & Chr(13)
 strBody = strBody & "Contract Start: " & [CONTRACT START (EFFECTIVE DATE)] & Chr(13)
 strBody = strBody & "Contract End: " & [CONTRACT END (TERMINATION) DATE] & Chr(13)
 strBody = strBody & "Purchase Type: " & [CONTRACT REQUEST TYPE] & Chr(13)
 strBody = strBody & "Amount: " & AMOUNT & Chr(13) & Chr(13)
 strBody = strBody & "Please refernce the above Contract ID number  when inquiring about this contra" & Chr(13) & Chr(13)
 strBody = strBody & "Best Regards," & Chr(13) & Chr(13)
 strBody = strBody & "Lazara Stinnette, USF Purchasing"
 
 With MailOutLook
     .To = [VENDOR CONTACT EMAIL]
     .subject = "Contract Info"
     .Body = strBody[/COLOR]
     .Attachments.Add (strFullAttachmentPath)
     [COLOR=Indigo].Display[/COLOR]
 
 'clean up
 Set rst = Nothing
 Set db = Nothing
 
 End Sub

Thanks again!

 

[NigelShaw]

Hi,

i will have a look at my code vs yours tonight and make a sample for you.


it wont be until later though


regs


Nidge

 

[Joy]

Thanks!

Do I need an ActiveX license for this function to work? It doesn't look like I have one...