WildCard

D

DS

I have a number of files that I need to attach to an E-Mail. Is there a
wild card that I can use, they are all in the same directory.
I tried this but it doesnt work.

..AddAttachment "C:\DX\TFILE\*.txt"

Is there another way?
Thanks
DS

Const cdoSendUsingPort = 2
Const cdoBasic = 1
Dim objCDOConfig As Object, objCDOMessage As Object
Dim strSch As String

strSch = "http://schemas.microsoft.com/cdo/configuration/"
Set objCDOConfig = CreateObject("CDO.Configuration")
With objCDOConfig.Fields
.Item(strSch & "sendusing") = cdoSendUsingPort
.Item(strSch & "smtpserver") = "mail.optonline.net"
' Only used if SMTP server requires Authentication
.Item(strSch & "smtpauthenticate") = cdoBasic
.Item(strSch & "sendusername") = "Store"
.Item(strSch & "sendpassword") = "123"
.Update
End With

Set objCDOMessage = CreateObject("CDO.Message")
With objCDOMessage
Set .Configuration = objCDOConfig
.FROM = "Site 1"
.sender = "(e-mail address removed)"
.to = Forms!DX!EmailAddress
.Subject = Date & "" & "Site 2 Famous Restaurant"
.HTMLBody = "Todays Update!"
.AddAttachment "C:\DX\TFILE\*.txt"
.Send
End With
Set objCDOMessage = Nothing
Set objCDOConfig = Nothing
DoCmd.Close acForm, "DX"
 
T

TC

I doubt you can use a wildcard there.

Try something like this, replacing your current .AddAttachment
statement:

dim s as string
s = dir$(C:\DX\TFILE\*.txt")
while s <> ""
.AddAttachment s
s = dir$()
wend

HTH,
TC (MVP Access)
http://tc2.atspace.com
 
D

DS

TC said:
I doubt you can use a wildcard there.

Try something like this, replacing your current .AddAttachment
statement:

dim s as string
s = dir$(C:\DX\TFILE\*.txt")
while s <> ""
.AddAttachment s
s = dir$()
wend

HTH,
TC (MVP Access)
http://tc2.atspace.com
Click to expand...
Thanks for the input.
It gets stuck on .addattachment s
it says unknown protocal....
DS
 
D

DS

DS said:
I have a number of files that I need to attach to an E-Mail. Is there a
wild card that I can use, they are all in the same directory.
I tried this but it doesnt work.

.AddAttachment "C:\DX\TFILE\*.txt"

Is there another way?
Thanks
DS

Const cdoSendUsingPort = 2
Const cdoBasic = 1
Dim objCDOConfig As Object, objCDOMessage As Object
Dim strSch As String

strSch = "http://schemas.microsoft.com/cdo/configuration/"
Set objCDOConfig = CreateObject("CDO.Configuration")
With objCDOConfig.Fields
.Item(strSch & "sendusing") = cdoSendUsingPort
.Item(strSch & "smtpserver") = "mail.optonline.net"
' Only used if SMTP server requires Authentication
.Item(strSch & "smtpauthenticate") = cdoBasic
.Item(strSch & "sendusername") = "Store"
.Item(strSch & "sendpassword") = "123"
.Update
End With

Set objCDOMessage = CreateObject("CDO.Message")
With objCDOMessage
Set .Configuration = objCDOConfig
.FROM = "Site 1"
.sender = "(e-mail address removed)"
.to = Forms!DX!EmailAddress
.Subject = Date & "" & "Site 2 Famous Restaurant"
.HTMLBody = "Todays Update!"
.AddAttachment "C:\DX\TFILE\*.txt"
.Send
End With
Set objCDOMessage = Nothing
Set objCDOConfig = Nothing
DoCmd.Close acForm, "DX"
Click to expand...
Is there anyway to code this to go faster, I have 40 attachments and it
takes about 8 seconds to run.
Thanks
DS
 
T

TC

Sorry, I don't know much about CDO.

What is the message /exactly/?

Do you get the message with your initial code, or only when you use the
new code that I suggested?

If the latter, please post the exact code that you are using now.

HTH,
TC (MVP Access)
http://tc2.atspace.com
 
D

DS

TC said:
Sorry, I don't know much about CDO.

What is the message /exactly/?

Do you get the message with your initial code, or only when you use the
new code that I suggested?

If the latter, please post the exact code that you are using now.

HTH,
TC (MVP Access)
http://tc2.atspace.com
Click to expand...
Runtime Error -2146697203(800c000d)':
Specified Protocol Unknown.

It only happens when I use your code.

Thanks
DS
 
T

TC

There's really nothing about my code, that would cause that particular
error. It must be the way you are using it, somehow.

Please post: (1) the exact code which does /not/ get that error, and
(2) the exact code which /does/ get that error (and say what line it
occurs on).

Don't just post the differences. Please post the whole code, of each
case.

Cheers,
TC (MVP Access)
http://tc2.atspace.com
 
D

DS

TC said:
There's really nothing about my code, that would cause that particular
error. It must be the way you are using it, somehow.

Please post: (1) the exact code which does /not/ get that error, and
(2) the exact code which /does/ get that error (and say what line it
occurs on).

Don't just post the differences. Please post the whole code, of each
case.

Cheers,
TC (MVP Access)
http://tc2.atspace.com
Click to expand...
THIS WORKS
DoCmd.OpenForm "Transmitting"
'1 Allergys
DoCmd.TransferText acExportDelim, , "Allergys",
"C:\DX\TFILE\DX1Allergys.txt", False
'2 AllergyTypes
DoCmd.TransferText acExportDelim, , "AllergyTypes",
"C:\DX\TFILE\DX1AllergyTypes.txt", False
'3 CouponType
DoCmd.TransferText acExportDelim, , "CouponType",
"C:\DX\TFILE\DX1CouponType.txt", False
'4 Customers
DoCmd.TransferText acExportDelim, , "Customers",
"C:\DX\TFILE\DX1Customers.txt", False
'5 DeleteTypes
DoCmd.TransferText acExportDelim, , "DeleteTypes",
"C:\DX\TFILE\DX1DeleteTypes.txt", False
'6 Departments
DoCmd.TransferText acExportDelim, , "Departments",
"C:\DX\TFILE\DX1Departments.txt", False
'7 Deposits
DoCmd.TransferText acExportDelim, , "Deposits",
"C:\DX\TFILE\DX1Deposits.txt", False
'8 DiscountTypes
DoCmd.TransferText acExportDelim, , "DiscountTypes",
"C:\DX\TFILE\DX1DiscountTypes.txt", False
'9 EMail
DoCmd.TransferText acExportDelim, , "Email",
"C:\DX\TFILE\DX1EMail.txt", False
'10 Employess
DoCmd.TransferText acExportDelim, , "Employees",
"C:\DX\TFILE\DX1Employees.txt", False
'11 Faxes
DoCmd.TransferText acExportDelim, , "Faxes",
"C:\DX\TFILE\DX1Faxes.txt", False
'12 Info
DoCmd.TransferText acExportDelim, , "Info",
"C:\DX\TFILE\DX1Info.txt", False
'13 Items
DoCmd.TransferText acExportDelim, , "Items",
"C:\DX\TFILE\DX1Items.txt", False
'14 JobNames
DoCmd.TransferText acExportDelim, , "JobNames",
"C:\DX\TFILE\DX1JobNames.txt", False
'15 JobsEmps
DoCmd.TransferText acExportDelim, , "JobsEmp",
"C:\DX\TFILE\DX1JobsEmp.txt", False
'16 Levels
DoCmd.TransferText acExportDelim, , "Levels",
"C:\DX\TFILE\DX1Levels.txt", False
'17 MenuCats
DoCmd.TransferText acExportDelim, , "MenuCats",
"C:\DX\TFILE\DX1MenuCats.txt", False
'18 MenuDetails
DoCmd.TransferText acExportDelim, , "MenuDetails",
"C:\DX\TFILE\DX1MenuDetails.txt", False
'19 MenuInfo
DoCmd.TransferText acExportDelim, , "MenuInfo",
"C:\DX\TFILE\DX1MenuInfo.txt", False
'20 Menus
DoCmd.TransferText acExportDelim, , "Menus",
"C:\DX\TFILE\DX1Menus.txt", False
'21 ModCats
DoCmd.TransferText acExportDelim, , "ModCats",
"C:\DX\TFILE\DX1ModCats.txt", False
'22 PartyPlanner
DoCmd.TransferText acExportDelim, , "PartyPlanner",
"C:\DX\TFILE\DX1PartyPlanner.txt", False
'23 PayApplied
DoCmd.TransferText acExportDelim, , "PayApplied",
"C:\DX\TFILE\DX1PayApplied.txt", False
'24 PayIns
DoCmd.TransferText acExportDelim, , "PayIns",
"C:\DX\TFILE\DX1PayIns.txt", False
'25 PayInTypes
DoCmd.TransferText acExportDelim, , "PayInTypes",
"C:\DX\TFILE\DX1PayInTypes.txt", False
'26 PayName
DoCmd.TransferText acExportDelim, , "PayName",
"C:\DX\TFILE\DX1PayName.txt", False
'27 Payouts
DoCmd.TransferText acExportDelim, , "Payouts",
"C:\DX\TFILE\DX1Payouts.txt", False
'28 PayoutTypes
DoCmd.TransferText acExportDelim, , "PayoutTypes",
"C:\DX\TFILE\DX1PayoutTypes.txt", False
'29 PayTypes
DoCmd.TransferText acExportDelim, , "PayTypes",
"C:\DX\TFILE\DX1PayTypes.txt", False
'30 PCenter
DoCmd.TransferText acExportDelim, , "PCenter",
"C:\DX\TFILE\DX1PCenter.txt", False
'31 PrepSequence
DoCmd.TransferText acExportDelim, , "PrepSequence",
"C:\DX\TFILE\DX1PrepSequence.txt", False
'32 Printers
DoCmd.TransferText acExportDelim, , "Printers",
"C:\DX\TFILE\DX1Printers.txt", False
'33 PrintRouting
DoCmd.TransferText acExportDelim, , "PrintRouting",
"C:\DX\TFILE\DX1PrintRouting.txt", False
'34 PrivAssign
DoCmd.TransferText acExportDelim, , "PrivAssign",
"C:\DX\TFILE\DX1PrivAssign.txt", False
'35 RefundTypes
DoCmd.TransferText acExportDelim, , "RefundTypes",
"C:\DX\TFILE\DX1RefundTypes.txt", False
'36 ReportCats
DoCmd.TransferText acExportDelim, , "ReportCats",
"C:\DX\TFILE\DX1ReportCats.txt", False
'37 Sales
DoCmd.TransferText acExportDelim, , "Sales",
"C:\DX\TFILE\DX1Sales.txt", False
'38 SalesCats
DoCmd.TransferText acExportDelim, , "SalesCats",
"C:\DX\TFILE\DX1SalesCats.txt", False
'39 SalesDetails
DoCmd.TransferText acExportDelim, , "SalesDetails",
"C:\DX\TFILE\DX1SalesDetails.txt", False
'40 Sections
DoCmd.TransferText acExportDelim, , "Sections",
"C:\DX\TFILE\DX1Sections.txt", False
'41 Tax
DoCmd.TransferText acExportDelim, , "Tax",
"C:\DX\TFILE\DX1Tax.txt", False
'42 Terminal
DoCmd.TransferText acExportDelim, , "Terminal",
"C:\DX\TFILE\DX1Terminal.txt", False
'43 TermType
DoCmd.TransferText acExportDelim, , "TermType",
"C:\DX\TFILE\DX1TermType.txt", False
'44 TimeLog
DoCmd.TransferText acExportDelim, , "TimeLog",
"C:\DX\TFILE\DX1TimeLogs.txt", False
'45 Users
DoCmd.TransferText acExportDelim, , "Users",
"C:\DX\TFILE\DX1Users.txt", False
'46 VoidReasons
DoCmd.TransferText acExportDelim, , "VoidReasons",
"C:\DX\TFILE\DX1VoidReasons.txt", False

'Send Files
Const cdoSendUsingPort = 2
Const cdoBasic = 1
Dim objCDOConfig As Object, objCDOMessage As Object
Dim strSch As String

strSch = "http://schemas.microsoft.com/cdo/configuration/"
Set objCDOConfig = CreateObject("CDO.Configuration")
With objCDOConfig.Fields
.Item(strSch & "sendusing") = cdoSendUsingPort
.Item(strSch & "smtpserver") = "mail.optonline.net"
' Only used if SMTP server requires Authentication
.Item(strSch & "smtpauthenticate") = cdoBasic
.Item(strSch & "sendusername") = "DS"
.Item(strSch & "sendpassword") = "123"
.Update
End With

Set objCDOMessage = CreateObject("CDO.Message")
With objCDOMessage
Set .Configuration = objCDOConfig
.FROM = "Site 1"
.sender = "(e-mail address removed)"
.to = Forms!DX!EmailAddress
.Subject = Date & "" & "Site 1 Famous Restaurant"
' Use TextBody to send Email in Plain Text Format
'.TextBody = "This is a test for CDO message"
' Use HTMLBody to send Email in Rich Text (HTML) Format
.HTMLBody = "Todays Update!"
' Adding Attachments is easy enough
.AddAttachment "C:\DX\TFILE\DX1Allergys.txt"
.AddAttachment "C:\DX\TFILE\DX1AllergyTypes.txt"
.AddAttachment "C:\DX\TFILE\DX1CouponType.txt"
.AddAttachment "C:\DX\TFILE\DX1Customers.txt"
.AddAttachment "C:\DX\TFILE\DX1DeleteTypes.txt"
.AddAttachment "C:\DX\TFILE\DX1Departments.txt"
.AddAttachment "C:\DX\TFILE\DX1Deposits.txt"
.AddAttachment "C:\DX\TFILE\DX1DiscountTypes.txt"
.AddAttachment "C:\DX\TFILE\DX1Email.txt"
.AddAttachment "C:\DX\TFILE\DX1Employees.txt"
.AddAttachment "C:\DX\TFILE\DX1Faxes.txt"
.AddAttachment "C:\DX\TFILE\DX1Info.txt"
.AddAttachment "C:\DX\TFILE\DX1Items.txt"
.AddAttachment "C:\DX\TFILE\DX1JobNames.txt"
.AddAttachment "C:\DX\TFILE\DX1JobsEmp.txt"
.AddAttachment "C:\DX\TFILE\DX1Levels.txt"
.AddAttachment "C:\DX\TFILE\DX1MenuCats.txt"
.AddAttachment "C:\DX\TFILE\DX1MenuDetails.txt"
.AddAttachment "C:\DX\TFILE\DX1MenuInfo.txt"
.AddAttachment "C:\DX\TFILE\DX1Menus.txt"
.AddAttachment "C:\DX\TFILE\DX1ModCats.txt"
.AddAttachment "C:\DX\TFILE\DX1PartyPlanner.txt"
.AddAttachment "C:\DX\TFILE\DX1PayApplied.txt"
.AddAttachment "C:\DX\TFILE\DX1PayIns.txt"
.AddAttachment "C:\DX\TFILE\DX1PayInTypes.txt"
.AddAttachment "C:\DX\TFILE\DX1PayName.txt"
.AddAttachment "C:\DX\TFILE\DX1Payouts.txt"
.AddAttachment "C:\DX\TFILE\DX1PayoutTypes.txt"
.AddAttachment "C:\DX\TFILE\DX1PayTypes.txt"
.AddAttachment "C:\DX\TFILE\DX1PCenter.txt"
.AddAttachment "C:\DX\TFILE\DX1PrepSequence.txt"
.AddAttachment "C:\DX\TFILE\DX1Printers.txt"
.AddAttachment "C:\DX\TFILE\DX1PrintRouting.txt"
.AddAttachment "C:\DX\TFILE\DX1PrivAssign.txt"
.AddAttachment "C:\DX\TFILE\DX1RefundTypes.txt"
.AddAttachment "C:\DX\TFILE\DX1ReportCats.txt"
.AddAttachment "C:\DX\TFILE\DX1Sales.txt"
.AddAttachment "C:\DX\TFILE\DX1SalesCats.txt"
.AddAttachment "C:\DX\TFILE\DX1SalesDetails.txt"
.AddAttachment "C:\DX\TFILE\DX1Sections.txt"
.AddAttachment "C:\DX\TFILE\DX1Tax.txt"
.AddAttachment "C:\DX\TFILE\DX1Terminal.txt"
.AddAttachment "C:\DX\TFILE\DX1TermType.txt"
.AddAttachment "C:\DX\TFILE\DX1TimeLog.txt"
.AddAttachment "C:\DX\TFILE\DX1Users.txt"
.AddAttachment "C:\DX\TFILE\DX1VoidReasons.txt"
.Send
End With
Set objCDOMessage = Nothing
Set objCDOConfig = Nothing
DoCmd.Close acForm, "Transmitting"
DoCmd.Close acForm, "DX"

End Sub


THIS DOESNT
Const cdoSendUsingPort = 2
Const cdoBasic = 1
Dim objCDOConfig As Object, objCDOMessage As Object
Dim strSch As String

strSch = "http://schemas.microsoft.com/cdo/configuration/"
Set objCDOConfig = CreateObject("CDO.Configuration")
With objCDOConfig.Fields
.Item(strSch & "sendusing") = cdoSendUsingPort
.Item(strSch & "smtpserver") = "mail.optonline.net"
' Only used if SMTP server requires Authentication
.Item(strSch & "smtpauthenticate") = cdoBasic
.Item(strSch & "sendusername") = "DS"
.Item(strSch & "sendpassword") = "123"
.Update
End With

Set objCDOMessage = CreateObject("CDO.Message")
With objCDOMessage
Set .Configuration = objCDOConfig
.FROM = "Site 1"
.sender = "(e-mail address removed)"
.to = Forms!DX!EmailAddress
.Subject = Date & "" & "Site 2 Famous Restaurant"

Dim s As String
s = Dir$("C:\DX\TFILE\*.txt")
While s <> ""
.AddAttachment s
s = Dir$()
Wend

.Send
End With
Set objCDOMessage = Nothing
Set objCDOConfig = Nothing
DoCmd.Close acForm, "DX"

Thanks
DS
 
T

TC

WORKS has:

.Subject = Date & "" & "Site 1 Famous Restaurant"
' Use TextBody to send Email in Plain Text Format
'.TextBody = "This is a test for CDO message"
' Use HTMLBody to send Email in Rich Text (HTML) Format
.HTMLBody = "Todays Update!"
' Adding Attachments is easy enough
.AddAttachment "C:\DX\TFILE\DX1Allergys.txt"

DOESN'T has:

.Subject = Date & "" & "Site 2 Famous Restaurant"

Dim s As String
s = Dir$("C:\DX\TFILE\*.txt")
While s <> ""
.AddAttachment s

So you seem to have accidentally deleted the code to set the body text
or HTML.

Is that it?

HTH,
TC (MVP Access)
http://tc2.atspace.com
 
D

DS

TC said:
WORKS has:

.Subject = Date & "" & "Site 1 Famous Restaurant"
' Use TextBody to send Email in Plain Text Format
'.TextBody = "This is a test for CDO message"
' Use HTMLBody to send Email in Rich Text (HTML) Format
.HTMLBody = "Todays Update!"
' Adding Attachments is easy enough
.AddAttachment "C:\DX\TFILE\DX1Allergys.txt"

DOESN'T has:

.Subject = Date & "" & "Site 2 Famous Restaurant"

Dim s As String
s = Dir$("C:\DX\TFILE\*.txt")
While s <> ""
.AddAttachment s

So you seem to have accidentally deleted the code to set the body text
or HTML.

Is that it?

HTH,
TC (MVP Access)
http://tc2.atspace.com
Click to expand...
I'll reintroduce the line but thats just for the body text, I'll let you
know if it works.
Thanks
DS
 
R

Rick Brandt

TC said:
There's really nothing about my code, that would cause that particular
error. It must be the way you are using it, somehow.

Please post: (1) the exact code which does /not/ get that error, and
(2) the exact code which /does/ get that error (and say what line it
occurs on).

Don't just post the differences. Please post the whole code, of each
case.

Cheers,
TC (MVP Access)
http://tc2.atspace.com
Click to expand...

Isn't DIR() going to return ONLY the file name without the path? Doesn't the
AddAttachment need the full path?
 
T

TC

Shoot me! :)

DS, Rick is quite right. The loop to find the files, should be like
this:

Dim s As String
const FOLDER = "C:\DX\"
s = Dir$(FOLDER & "*.txt")
While s <> ""
.AddAttachment FOLDER & s
wend

If you wanted to check that the loop was finding the right files, you
could temporarily say "MsgBox s", instead of ".AddAttachment s".

Not sure if this would be the cause of the error you're getting,
though.

Cheers,
TC (MVP Access)
http://tc2.atspace.com
 
D

DS

TC said:
Shoot me! :)

DS, Rick is quite right. The loop to find the files, should be like
this:

Dim s As String
const FOLDER = "C:\DX\"
s = Dir$(FOLDER & "*.txt")
While s <> ""
.AddAttachment FOLDER & s
wend

If you wanted to check that the loop was finding the right files, you
could temporarily say "MsgBox s", instead of ".AddAttachment s".

Not sure if this would be the cause of the error you're getting,
though.

Cheers,
TC (MVP Access)
http://tc2.atspace.com
Click to expand...
Ok, thats better, in the fact that the E-Mail now goes through but no
attachments are with it. So it's half way there!
Thanks
DS
 
T

TC

Ok, well done!

It shouldn't be too hard to debug that problem. You now have two
working versions of the code. The only difference is, that version #1
names all the files explicitly, but version #2 uses a loop for that. By
comparing the two versions, you should be able to find the problem.

Cheers,
TC (MVP Access)
http://tc2.atspace.com
 
D

DS

TC said:
Ok, well done!

It shouldn't be too hard to debug that problem. You now have two
working versions of the code. The only difference is, that version #1
names all the files explicitly, but version #2 uses a loop for that. By
comparing the two versions, you should be able to find the problem.

Cheers,
TC (MVP Access)
http://tc2.atspace.com
Click to expand...
Thanks TC, I'll continue to play with it.
DS
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sending E-mail 3
DoEvents 3
Sending E-Mail 11
Using the CDO object to send email... 3
Form Not Visible 2
Send email to exchange versus SMTP 1
CDO & MailMessage problem 2
Sending Access mail using smtp 1

Top