Full Version: email attachment automation
UtterAccess Discussion Forums > Microsoft® Access > Access Automation
donpayseur
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:
TSv2.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
strive4peace
where is the email address coming from? Where is the code to generate the CSV file?
donpayseur
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....
donpayseur
The email that is in the code now is my email. The actual destination email will be changed once this baby is working...
strive4peace
instead of
oCmd.SendObject , , "don@paystarlogistics.com", , , "Data Post From Paystar Logistics", , , ""
CODE
   Dim outApp As Outlook.Application, outMsg As MailItem
   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"
      .Attachments.Add "C:\Truckposts\" & strDocName
      ' If you want the screen to be seen then add the following line
'      .Display
      .Send
   End With
  
   Set outApp = Nothing
   Set outMsg = Nothing
donpayseur
Wow this looks great I will give it a try and get right back with you.
Thanks!
Don
strive4peace
you're welcome wink.gif
donpayseur
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
R. Hicks
Have you enabled the MS Outlook Reference Library for the version of Outlook you are using ???
For Outlook 2002 it would be Outlook 11 ...
DH
donpayseur
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
donpayseur
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
donpayseur
Hey Ricky, I figured it out you refering to adding the references in VBA....got it.
strive4peace
I think you also need to generate your CSV file before you attach it...
And when you post code, use the code tags
CODE
codeblock

this will make it easier for others to help you
donpayseur
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
strive4peace
Ricky ... do you know?
tw, 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
donpayseur
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
R. Hicks
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 ..
THere is a link to more information on this approach ...

Express ClickYes

FOr .. 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
donpayseur
Ricky, thanks for the information. Also I appreciate your help with my email solution. Have a great Day.
Don
donpayseur
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:
CODE

DoCmd.OutputTo acOutputTable, "truckstopOutPut", acFormatXLS, "C:\Truckposts\" & StrDocName1

o you think you could still help me? Thanks Don
strive4peace
the above code outputs to Excel format
That do you want me to look at in the db that you sent?
donpayseur
no this is the txt file I am trying to create with code.
strive4peace
if you are trying to create a text file, why are you using acFormatXLS instead of acFormatTXT
donpayseur
Also too just trying to be as explict as I can so I don't confuse you. I am trying to take the table truckstopoutput and export it to a delimited text file as per attached. I can do it manually, so I would assume I can do it programatically as well. My thinking is this:
CODE
DoCmd.TransferText acExportDelim, , "truckstopOutput", "c:\truckposts\" &strDocName", , , ""
trDocName is this
strDocName = "ITSv2_0 BP76956W" & Me.[LoadNum] & "_Add" & ".txt"

The saved file has to be a string because the name will change with every order...Hope I am not confusing you...Thanks
Don
donpayseur
Good Question Crystal, If I use acFormatTxt then it does save it as a text file but not a delimited test file.
strive4peace
this creates a delimited text file with fieldnames...
!--c1-->
CODE
DoCmd.TransferText acExportDelim, , "Table or Query Name", "c:\filepath\filename.csv", True
donpayseur
Hello,
Oget an error that says database is read only, which it is not. Here is where I am at.
!--c1-->
CODE

DoCmd.TransferText acExportDelim, , "TruckstopOutPut", "c:\truckposts\& strDocName", True
strive4peace
"c:\truckposts\& strDocName"
hould be
"c:\truckposts\" & strDocName
chuckneerg
If your doing the automation email thing... And ur Sick of having that little popup inOutlook... Use CDO...
Function Send_Emails_CDO(Max_OOR_Date As String, mail_selection As Integer)
Dim iMsg As New CDO.Message
Dim iConf As New CDO.Configuration
Dim Flds As ADODB.Fields
Set Flds = iConf.Fields
Dim RST_Email As New ADODB.Recordset
Dim SQL_Email As String
Dim Body_Text As String
Set CNN = CurrentProject.Connection
Select Case mail_selection
Case 1 ' This is for the OOR

Body_Text = " Good Morning, This is your current OOR Report for WK " & Max_OOR_Date & ". Please be sure to TRANSFER THE NEW SHEETS INTO your Supplier OOR. Do not try to respond to this email for it will not reach anyone. If you have any question please do not hesitate to contact me. Thanks Chuck"
Subject_Text = " OOR - " & Max_OOR_Date

Case 2 ' This for the Supplier Listing

Body_Text = "Good Morning, This email is providing you with your current Supplier Listing. This listing is effective immediately."
Body_Text = Body_Text & " NOT ALL SUPPLIERS ON THIS SHEET WILL SHOW UP ON THE OOR. If a supplier does not show up on the OOR, they do not have any ORDERS. If you believe this is inaccurate, please contact me."
Body_Text = Body_Text & " Do not try to respond to this email for it will not reach anyone. If you have any questions please Contact me. Thanks Chuck"
Subject_Text = " Supplier Listing - " & Max_OOR_Date
End Select


SQL_Email = " SELECT tbl_Admin_Assignments.Admin_Email, tbl_Admin_Information.Admin_First, tbl_Admin_Information.Admin_Last"
SQL_Email = SQL_Email & " FROM tbl_Admin_Information INNER JOIN tbl_Admin_Assignments ON tbl_Admin_Information.Admin_Email = tbl_Admin_Assignments.Admin_Email"
SQL_Email = SQL_Email & " GROUP BY tbl_Admin_Assignments.Admin_Email, tbl_Admin_Information.Admin_First, tbl_Admin_Information.Admin_Last"
SQL_Email = SQL_Email & " ORDER BY tbl_Admin_Assignments.Admin_Email;"
RST_Email.Open SQL_Email, CNN, adOpenForwardOnly, adLockReadOnly


With Flds
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServer) = ************* 'replace with correct server name
.Item(cdoSMTPConnectionTimeout) = 10
.Update
End With


Do Until RST_Email.EOF = True

Set objMessage = CreateObject("CDO.Message")

Select Case mail_selection
Case 1
StrFileName = Application.CurrentProject.Path & "\Reports\Admin_OOR\" & RST_Email!admin_first & " " & RST_Email!Admin_Last & " - OOR.xls"
Case 2
StrFileName = Application.CurrentProject.Path & "\Reports\Admin_OOR\" & RST_Email!admin_first & " " & RST_Email!Admin_Last & " - Suppliers.xls"
End Select

With iMsg
Set .Configuration = iConf
.To = RST_Email!Admin_Email
'IF U WANT TO HAVE UR EMAIL AS THE SEND PLACE IT BELOW
.From = "Message Service"
.Sender = "Chuck_Green"
'--------------------------------------
.Subject = Subject_Text
.TextBody = Body_Text
.AddAttachment StrFileName
.Send
End With

Set iMsg = Nothing
Debug.Print StrFileName
RST_Email.MoveNext
Loop
strive4peace
thanks, Chuck ... sure do wish you'd used
CODE
[/b] abve it and [b]
after it though...
R. Hicks
No problem .. but Crystal did "all the work" ... wink.gif
DH
BrenoAguiar
Is there a way to have the HTML file as the body of this email msg attached? This is what I have and it has got an attached file (the .htm file).
Private Sub cmdSendEmail_Click()
If Trim([Forms]![guestsmain]![Address3] & "") = "" Then Exit Sub

Dim intImportance As Integer


Dim myApp As New Outlook.Application
Dim myItem As Outlook.MailItem
Set myItem = myApp.CreateItem(olMailItem)
With myItem
.Importance = intImportance
.To = Me![Address3]
.Subject = "Testing Subject Brochure"
.ReadReceiptRequested = False
.BodyFormat = olFormatHTML
.Body = " Dear Mr(s). " & Me![firstname] & " " & Me![lastname] _
& " - " & " Thank you for your request! Your Brochure is on the mail. If you need more information, please refer to the web site ."
.Attachments.Add "K:\Master\ACCESS\DATABASE\Breno\Brett RES\images\lettertest.htm"
End With
myItem.Send
End Sub
I guess the ".Body" could be set in html ?
Thanks for any help at all!
strive4peace
why don't you test it?
Body = " Dear Mr(s). " & Me![firstname] & " " & Me![lastname] _
& " - " & " <b>Thank you for your request!</b> Your Brochure is on the mail. If you need more information, please refer to the web site ."
send a message to yourself and see if "Thank you for your request" is in bold...
Ocannot test anything for you, as I said before, I based my outlook code on something that Ricky posted, I don't use Outlook.
btw, Ricky ... what is your comment supposed to mean?
BrenoAguiar
It doesn't work. I tried that. But I found the .HTMLBody object and you can use html like:
HTMLBody = "<html> <body> Test 123...</body></html>"
but it looks very limited. I tried to paste a template html I had and in the VBa window, it seems that large html writing won't be possible.
any ideas?
Thanks for you help!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.