Email Attachments from Access 97

[BoxHead]

I have a dozen Auditors who will be completing and submitting audits on a weekly basis to an admin who will import the data to a master database to generate reports and work orders.

I can't get a slot on the company server to store the DB so I have to depend on email.

I had used the SendObject method but found that even on my own laptop, I was unable to import the excel file because of a version discrepancy between my Access 97 and Excel. It's necessary to open the file in excel, wait for the prompt to upgrade to the newer version, upgrade , close, save and then import the file.

After way too many hours of searching the FAQs, threads, and MS Knowledge base, I ended up using the TransferSpreadsheet to create a file in Excel (to avoid the upgrade requirement) in the auditors' interface, following a hyperlink to that file, and using sendkeys to to open an e-mail message window with the file as an attachment.

The user will then have to insert the admin's email address from the address book, but the admin will be able to import the 12 files without having to open, update, etc... each one.

It works, but it's ugly, and it doesn't actually do what I believe it should be able to do.

There's a possibility that this could be rolled out to 1000+ auditors nationwide and since all of our laptops are mirrored from the corporate-approved disc, I can't see updating the Excel versions in Access.

Any solution has to come from within the database.

How can I get the data to the admin with a click of a button?

P.S. I tried sendObject as HTML, RichText and Text but wasn't able to import any of them.

Any guidance would be greatly appreciated.

TIA

John

 

[Mossoft]

You can open and manipulate both the Excel spreadsheet and email (Outlook) from within the database or spreadsheet etc.
Set References to Microsoft Excel and Microsoft Outlook object libraries then use objects Excel.Application and Outlook.Mailitem objects.

Here is some code to create and send email with a single file attached. It could be modified to be used in the spreadsheet (or database) to send multiple files:

Function Email_File(i_strFilename As String, i_strAddress As String) As Boolean
'
'Email the file to the address given. Separate addresses delimited with semicolons
'
' Modifications
' Who When Mod What

On Error GoTo Email_Error
Dim objMailItem As MailItem
Dim lngSemiColon As Long
Dim strAddress As String

Email_File = True

'create mail item
Set objMailItem = Outlook.CreateItem(olMailItem)
objMailItem.SUBJECT = EMAIL_SUBJECT_TEXT

'add attachment
objMailItem.Attachments.Add Source:=i_strFilename, Type:=olByValue

'parse and add addresses
strAddress = i_strAddress
While Len(strAddress) > 0
lngSemiColon = InStr(strAddress, ";&quot
If lngSemiColon > 0 Then
objMailItem.Recipients.Add Left(strAddress, lngSemiColon - 1)
strAddress = Mid(strAddress, lngSemiColon + 1)
Else
objMailItem.Recipients.Add strAddress
strAddress = vbNullString
End If
Wend

'acknowledgement
objMailItem.ReadReceiptRequested = EMAIL_READ_RECEIPT

'send mail
objMailItem.Send

DoEvents

Email_Exit:
Exit Function

Email_Error:
Email_File = False
Resume Email_Exit

End Function

HTH

 

[BoxHead]

Mossoft,

Thanks for your reply.

When you say, "Set References to Microsoft Excel and Microsoft Outlook object libraries", is this something that is done with the code?

"Dim objMailItem     As MailItem"

doesn't get by the compiler: "User-defined type. Not defined."

I'm guessing that has something to do with the object library references. Do I have to Set the references on each user's laptop?


Thanks,

John

 

[Craig0201]

Boxhead,

The references are available in the code window.

Go to Tools > References and tick the appropriate reference.

Craig