Hello everyone.
Well I have been toiling away on a function that takes an order and coverts the order to a csv file. What I've got so far is this. A make table is created and then I use an acOutput function to export the table as a xls file and then save the file to a folder on the server. (\trucksposts) the string will save the file to the folder and give it a unique name (using the load Number) along with the required file name. So the file looks like this:

ITSv2.0BP123456D33333_add.csv. The BP123456D our account number and the 33333 is the Load Numbe(and the loadnumber will change with each order, i.e. unique) and the _add is the command that the vendor requires to get the file in the correct location.
Now what I have to do is create a function that will automatically email the file tothe vendor's server. . Could someone help me with this?
Thanks much.
Don Payseur/Charlotte

where is the email address coming from? Where is the code to generate the CSV file?

I am hard coding in the sub. Here is what I have so far.

If Me.optTruckPost = 1 Then

Dim strDocName As String

strDocName = "ITSv2_0 BP76956W" & Me.[LoadNum] & "_Add" & ".csv"

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryTruckStopPost", acNormal, acEdit
DoCmd.OutputTo acOutputTable, "truckstopOutPut", acFormatXLS, "C:\Truckposts\" & strDocName
DoCmd.SendObject , , "don@paystarlogistics.com", , , "Data Post From Paystar Logistics", , , ""

Else
End If
eveything works i.e. the email pops up but I can figure out how to get the attachment wich will change with every email....

The email that is in the code now is my email. The actual destination email will be changed once this baby is working...

instead of

DoCmd.SendObject , , "don@paystarlogistics.com", , , "Data Post From Paystar Logistics", , , ""

Wow this looks great I will give it a try and get right back with you.
Thanks!
Don

you're welcome (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)

Hello, Here is what I have now: However it is not compiling. There error I get is this: Complie Error
User-defined type not defined.
To me it looks like it is....hmmm


Private Sub optTruckPost_AfterUpdate()
If Me.optTruckPost = 1 Then

Dim outApp As Outlook.Application, outMsg As MailItem
Dim strDocName As String

Set outApp = CreateObject("Outlook.Application")
Set outMsg = outApp.CreateItem(olMailItem)

With outMsg
.To = "don@paystarlogistics.com"
.Subject = "Data Post From Paystar Logistics"
.Body = "attached file: Data Post From Paystar Logistics"
.Add "C:\Truckposts\" & strDocName

.Display
.Send
End With

Set outApp = Nothing
Set outMsg = Nothing

strDocName = "ITSv2_0 BP76956W" & Me.[LoadNum] & "_Add" & ".csv"

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryTruckStopPost", acNormal, acEdit
DoCmd.OutputTo acOutputTable, "truckstopOutPut", acFormatXLS, "C:\Truckposts\" & strDocName

Else
End If

End Sub

Have you enabled the MS Outlook Reference Library for the version of Outlook you are using ???
For Outlook 2002 it would be Outlook 11 ...

RDH

Hey Crystal
Hello, Here is what I have now: However it is not compiling. There error I get is this: Complie Error
User-defined type not defined.
To me it looks like it is....hmmm


Private Sub optTruckPost_AfterUpdate()
If Me.optTruckPost = 1 Then

Dim outApp As Outlook.Application, outMsg As MailItem
Dim strDocName As String

Set outApp = CreateObject("Outlook.Application")
Set outMsg = outApp.CreateItem(olMailItem)

With outMsg
.To = "don@paystarlogistics.com"
.Subject = "Data Post From Paystar Logistics"
.Body = "attached file: Data Post From Paystar Logistics"
.Add "C:\Truckposts\" & strDocName

.Display
.Send
End With

Set outApp = Nothing
Set outMsg = Nothing

strDocName = "ITSv2_0 BP76956W" & Me.[LoadNum] & "_Add" & ".csv"

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryTruckStopPost", acNormal, acEdit
DoCmd.OutputTo acOutputTable, "truckstopOutPut", acFormatXLS, "C:\Truckposts\" & strDocName

Else
End If

End Sub

Hey Ricky, on the server we are using outlook 2003 and on most workstations. how do I enable the reference. This makes sense to enable

Hey Ricky, I figured it out you refering to adding the references in VBA....got it.

I think you also need to generate your CSV file before you attach it...

and when you post code, use the code tags



this will make it easier for others to help you

Well, I just got down from dancing on the coffee table with excitement. That's right it works great...
Thanks for the code advice and how to post. One final thought, the email will pop up first with a message box state that an automated process created the email and that it might be a virus. can this be turned off and can the email send without a user having hit the send button.
Again thanks I am very grateful for your help.
Don

Ricky ... do you know?

btw, Don, the email code came from a post authored by Ricky, I use eOutlook Express so I cannot test it and search for your answer

Hi Ricky, I have this working pretty well with the exception of one thing. When the email is generated, I get a warning that a possible virus might be sending the email. Can I override this warning?
Thanks very much I appreciate the great help.
Don

This is a built in security feature in Office since the release of Office 2000 SR2 ...

There are a couple of things to get past this warning .. none of which you will want to hear ...

You can use a third party application to intervene and answer Yes automatically ..
Here is a link to more information on this approach ...

Express ClickYes

Or .. here is the approach I use ....
I use a third party .dll file (Redemption.dll) on the machine(s) sending the emails to bypass the security feature.
Here is a link to more information on this approach ....

Outlook Redemption v. 3.4

RDH

Ricky, thanks for the information. Also I appreciate your help with my email solution. Have a great Day.
Don

Hi Crystal, hope you are doing well today. Well I still trying to pull my hair out on this one. The problem I have is that when I save the file from xls to csv it really doesn't save it as a csv just a corrupt file that is all. What I need to do is a delimited txt file upon which I have attached. Also to refresh you here is the code I was using:


Do you think you could still help me? Thanks Don

the above code outputs to Excel format

what do you want me to look at in the db that you sent?