Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-21-2008, 05:07 PM   #1
jfgambit
Kinetic Card Dealer
 
jfgambit's Avatar
 
Join Date: Jul 2002
Location: Sunnyvale, CA
Posts: 798
Thanks: 0
Thanked 2 Times in 2 Posts
jfgambit is on a distinguished road
Emailing contents of folder

I was wondering if anyone had code examples for emailing the contents of an entire folder? The folder can have multiple file types in it (excel, pdf, word, etc) but every file need to be linked to the Outlook e-mail that Access produces.

I was trying to use this, but it completely ignores this part of the code:

Code:
Dim str_Source_Path As String
Dim str_File_Name As String

str_Source_Path = "C:\Temp"

Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItem(0)
Set SafeMail = CreateObject("Redemption.SafeMailItem")
Set SafeMail.Item = myItem

With SafeMail
    
    .Recipients.Add (Me.CSOA) ' Send To
    .Recipients.Add (strSendTo) ' test code
    
    .Subject = "A new Peer/Management Validation has been completed for SO #" & Me.SONbr & " by " & Me.Validator & " for " & Me.CSOA
    
    .Body = "Validation Request - SO #" & Me.SONbr & vbNewLine _
     & "Customer Name: " & Me.CustNm & vbNewLine _
     & vbNewLine _
     & vbNewLine _
     & "Order Validation Completed: " & Me.CreateDt & vbNewLine _
     & "Total Time Taken to Validate: " & strTimeDiff & vbNewLine _
     & "http://sp-fin/sites/OMApp/AuditValidation/Forms/AllItems.aspx" & vbNewLine _
     & vbNewLine
     
End With

    'Loop and attach all files from this folder
    '-------------------------------------------
    str_File_Name = Dir(str_Source_Path)
    Do While str_File_Name <> ""
    SafeMail.Attachments.Add (str_Source_Path & str_File_Name)
        str_File_Name = Dir
    Loop

With SafeMail
     .Importance = Outlook.olImportance.olImportanceHigh
     .Send
End With

Set Utils = CreateObject("Redemption.MAPIUtils")
Utils.DeliverNow
Set myOlApp = Nothing
Set SafeMail = Nothing
Set Utils = Nothing
Thanks for any help, been wracking my head searching every site I know of.

__________________
"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

jfgambit is offline   Reply With Quote
Old 08-22-2008, 01:51 AM   #2
CyberLynx
Stuck On My Opinions
 
Join Date: Jan 2008
Posts: 585
Thanks: 0
Thanked 5 Times in 5 Posts
CyberLynx will become famous soon enough
Perhaps you should try and just create a string with all the attachments (separated by semicolons) then add that string. I'm not sure if that will work though - It may be worth a try:

Code:
'Loop and attach all files from this folder
'-------------------------------------------
Dim AttachmentStrg As String

If Right$(str_Source_Path,1) <> "\" Then str_Source_Path = str_Source_Path & "\"
str_File_Name = Dir(str_Source_Path)

Do While str_File_Name <> ""
   AttachmentStrg = AttachmentStrg  & str_Source_Path & str_File_Name & ";"
   str_File_Name = Dir
Loop
AttachmentStrg = Left$(AtachmentStrg, Len(AttachmentStrg) - 1)
SafeMail.Attachments.Add AttachmentStrg
Mind you...I hope your not talking about attaching something like 100 files.

.
__________________
Self taught in all Environments.....and it shows
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by CyberLynx; 08-22-2008 at 01:54 AM. Reason: oops
CyberLynx is offline   Reply With Quote
Old 08-22-2008, 07:55 AM   #3
jfgambit
Kinetic Card Dealer
 
jfgambit's Avatar
 
Join Date: Jul 2002
Location: Sunnyvale, CA
Posts: 798
Thanks: 0
Thanked 2 Times in 2 Posts
jfgambit is on a distinguished road
Let me give this a try and see, thanks.

And no, only 3 or 4. But you never know

__________________
"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

jfgambit is offline   Reply With Quote
Old 08-22-2008, 08:04 AM   #4
jfgambit
Kinetic Card Dealer
 
jfgambit's Avatar
 
Join Date: Jul 2002
Location: Sunnyvale, CA
Posts: 798
Thanks: 0
Thanked 2 Times in 2 Posts
jfgambit is on a distinguished road
Nope...got the message:

Runtime Error file "" does not exist.

We are close, but i can't seem to grasp what is missing.
__________________
"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

jfgambit is offline   Reply With Quote
Old 08-22-2008, 11:20 PM   #5
CyberLynx
Stuck On My Opinions
 
Join Date: Jan 2008
Posts: 585
Thanks: 0
Thanked 5 Times in 5 Posts
CyberLynx will become famous soon enough
My apologies.
The code I provided is flawed. There typos and things missing. This is what happens why supplying code on the fly. Try this:

Code:
'Loop and attach all files from this folder
'-------------------------------------------
Dim AttachmentStrg As String

If Right$(str_Source_Path, 1) <> "\" Then str_Source_Path = str_Source_Path & "\"

'This will fill str_File_Name with the first file found in Folder.
str_File_Name = Dir(str_Source_Path)

'Start creating the Attament String IF a file is found in folder. 
'If not then get outta here.
If str_File_Name <> "" Then
   AttachmentStrg = AttachmentStrg & str_Source_Path & str_File_Name & ";"
Else
   Exit Sub
End If

'Loop through and get any other File Names...
Do While str_File_Name <> ""
   str_File_Name = Dir
   If str_File_Name <> "" Then AttachmentStrg = AttachmentStrg & _
                               str_Source_Path & str_File_Name & ";"
Loop

'Remove the semicolon from the end of the Attachment String.
AttachmentStrg = Left$(AttachmentStrg, Len(AttachmentStrg) - 1)

'Apply to mail attachment
SafeMail.Attachments.Add AttachmentStrg
.

__________________
Self taught in all Environments.....and it shows
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
CyberLynx is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I copy files from 1 folder to another Anthony George Modules & VBA 23 01-24-2008 12:14 AM
Viewing contents of a single folder Anthony George Modules & VBA 7 12-17-2006 04:40 AM
Complex query sort DocNice Queries 4 07-20-2005 08:27 PM
List Directory / Folder contents in a Form inyathi Forms 2 03-23-2005 06:17 AM
Check Excel Cell Values in all files in folder CutAndPaste Modules & VBA 3 03-26-2003 01:21 AM




All times are GMT -8. The time now is 01:19 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World