Send a file via email? 3

[Zeroanarchy]

Does anyone know a method of sending a file via the users default mail program automaticly on the 1st of every month, what I am trying to do is on the opening of the program is send a file located c:\program files\program\file.mdb to zeroanarchy@hotmail.com without the user having to remember to do it, or worry about it.

Like so..

Private Sub Form_Open(Cancel As Integer)
if date= 01/MM/YY then
Docmd.SendObject acSendNoObject,,acFormatRTF,Zeroanarchy@hotmail.com,,,,,-1
'with file [file.mdb]
Else
End If
End Sub


Any idea is a good idea.

thanks ZeroAnarchy

 

[theraven14]

I came to this conclusion after I asked a similiar question,
"I totally ditched what I had and ventured into Automation. I was doing the DoCmd.SendObject command. I have everything worked out now. And I highly suggest automation. If anyone wants to read up on Automation for VB it can be found on the Microsoft Knowledge Base. It is the introductory article and tutorial: Search for Q260410, then download the CHM file. "

I suggest that you look at the automation tutorial in the download, it has very usefull examples, that should get you started.

TheRaven
"Remember, it's just ones and zeros"

 

[JPeters]

Copy and paste this into a module, edit to your hearts content... add eggs, some vanilla, bake at 350 until code starts to turn golden brown..
Dont forget to reference Outlook..

' Reference Outlook to your database: Toos/References
Function TekTipsMailHelp()
On Error GoTo Errhandler
Dim PersonSendTo As String
Dim SubjectLine As String
Dim MsgBody As String

PersonSendTo = "MyEmail@Email.com" 'adjust to your email address
MsgBody = Chr(13) & &quot;<br><br>&quot; & Chr(13) & &quot;Please Check the Database for New Record Entries.&quot; & Chr(13) & _
&quot;<br><br>&quot; & Chr(13) & &quot;<br><br>&quot;
SubjectLine = &quot;New Record Has Been Added.&quot;

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject(&quot;Outlook.Application&quot
Set MailOutLook = appOutLook.CreateItem(olMailItem)

With MailOutLook
.To = PersonSendTo
.Subject = SubjectLine
.HTMLBody = MsgBody & &quot;<br><br>&quot;
.Send
End With

Exit_sub:
Exit Function

Errhandler:
'GoTo Exit_sub
MsgBox Err.Description

End Function

Tastes good!

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[Zeroanarchy]

JPeters thankyou very much for the code, I have seen something similar but both peice of code that I have come across have not line for Attachments ie a line that automaticly attaches the file and sends it with the email.
Have you seen anything that includes that option.

theraven14 thanks for the reference nice greatly appreatiated.

thanks Zero

 

[JPeters]

Yep.. it's easy

Dim strFilePath as String
Set strFilePath = &quot;c:\mypath\myfile.txt&quot; ' or whatever

add this in the With MailOutlook section:

.Attachments.Add strFilePath

you'll need to do this for each file... you can setup some sort of a loop if you have a large number of files that change constantly and whose paths are in some sort of table... some loop to change the value of strfilepath until the recordset is empty.. If it's just one file, however - leave it like this. I can show you how to setup the loop if you'd like. Just let me know.

-Josh
------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[Zeroanarchy]

Ok this is the code to date and the error I am getting is

Compile error:
Object required &quot;with strfilepath&quot; highlighted on the following line.
Set strFilePath = &quot;c:\weedmanagerdata.mdb&quot;

Any Ideas.

Thanks For all your time on this


' Reference Microsoft Outlook 10.0 object library
Function TekTipsMailHelp()
On Error GoTo Errhandler
Dim PersonSendTo As String
Dim SubjectLine As String
Dim MsgBody As String
Dim strFilePath As String

PersonSendTo = &quot;richard@weedmanager.net&quot;
MsgBody = Chr(13) & &quot;<br><br>&quot; & Chr(13) & &quot;Please Check the Database for New Record Entries.&quot; & Chr(13) & _
&quot;<br><br>&quot; & Chr(13) & &quot;<br><br>&quot;
SubjectLine = &quot;Monthly Update.&quot;

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject(&quot;Outlook.Application&quot
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Set strFilePath = &quot;c:\weedmanagerdata.mdb&quot;

With MailOutLook
.To = PersonSendTo
.Subject = SubjectLine
.Attachments.Add strFilePath
.HTMLBody = MsgBody & &quot;<br><br>&quot;
.Send
End With

Exit_sub:
Exit Function

Errhandler:
'GoTo Exit_sub
MsgBox Err.Description

End Function

 

[JPeters]

Try this:

Set strFilePath = &quot;&quot;c:\weedmanagerdata.mdb&quot;&quot;

Extra quotes.. but that's probably not it..

Just do this, it will work. Assuming the file is actually in the C:\ directory and it's spelt correctly (you're sending the entire DB?)

.Attachments.Add &quot;c:\weedmanagerdata.mdb&quot;

I know this will work because I examined my code and this is how I do it. My code works, so should yours. ;-)

Have you considered doing text file exports and just sending the text files? .. or excel spreadsheet exports? .. Hrmm, interesting method of sending the entire DB.

-Josh
------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[oharab]

C'mon guys wake up!
strFilePath = &quot;c:\weedmanagerdata.mdb&quot;

Where dod set come from????



B
----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

----------------------------------

 

[Zeroanarchy]

You are not going to believe this, the code works fantasticly but I have come up against one prob.

If I goto to my Outbox I find the following message attached to the email.

Error is Outlook is blocking the email for the following reasons.

Message has not been sent.
&quot;Outlook Blocked access to the following potential unsafe attachments:weedmanagerdata.mdb&quot;

Has anyone seen this before or goto a work around. Or is it a new WinXP prob.

It also asks the user before it sends and if the users says it is ok it still comes up with the message on the email.

Hmmm
thanks Zero Anarchy

 

[JPeters]

That's your companies firewall. It probably doesn't allow you to send Databases as attachments. You might want to talk to your IT department or see who's in charge of security and ask what you can send as attachments through outlook?

Or it could be local settings in outlook. Go to your settings/options in outlook and see if attachment settings are funny.

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[Zeroanarchy]

Josh will do, thanks very much for your help on this the code works well greatly appreatiated.

I hope more people get benefit out of the above code.


Thanks again
ZeroAnarchy

*** If you come across this email and use the above code dont forget to give Josh a Star for his hard work. ***

 

[oharab]

IIRC it's part of Microsofts drive to stop virusssss being spread via emails. It stops you sending just about everything! I am pretty sure there's a way round it, either by applying a patch or changing the registry or something. Check the MS website for more details.

b ----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

----------------------------------

 

[JPeters]

Zeroanarchy,
Thanks for the star! Glad it works!

Ben,
I've been having an awful lot of trouble sending certain kinds of flie extensions as attachments lately. Same with file size. I have to rename half the files I send and have the end user rename them back to .MDB or what have you. It's extremely frustrating and it all started occuring around the time my company decided to up firewall security. I just attributed it to the upgrade, but now I'll look into these patches and see if those will fix it for me.
Kudos - glad to see you around here. You haven't been posting in your usual gusto as of late!

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[Zeroanarchy]

JPeters this may solve you problem it a method I use to get around the company firewalls. most company fire walls will block .Exe, .com, .mdb, .bat, plus a few others the solution is to convert it into a zip file.
I have a very old copy of pkzip.exe, from the old dos days very handy for this type of app.

Here is the code I use to do the job.


Private Declare Function WinExec Lib &quot;kernel32&quot; (ByVal lpCmdLine As String, ByVal nCmdShow As Long) As Long
--------------------------------------------------------


RunFile = WinExec(&quot;C:\Program Files\Comon Files\pkzip.exe -p -s[password] C:\Progra~1\weedma~1\data\weeddata.zip C:\Progra~1\weedma~1\data\weed*.mdb&quot;, 0)


As you can see I have hidden the old copy of pkzip.exe in
C:\Program Files\Comon Files and call on it when I need it.

If you are after a copy of the old PKzip let me know.

Zero Anarchy

 

[JPeters]

Yeah,

I use the old pkware pkzip.exe and pkunzip.exe from time to time. However, my company firewall stops some zips as well - which wierds me out because some will send and some won't... maybe it's filename based.. This was actually the first thing I tried. Thanks for the suggestion!

-Jos ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[jamus]

Hi,

Firrt let me say I've got most of my problem solved from this thread. Thanks.

For the un-solved bit....

I'm in the same situation of having to send a report every morning to a group to users.

Have copied the code into a module in Outlook and can run it in VB mode.

Can you help by advicing the following

how to config so that the email and the attached report will be sent everyday WITHOUT Outlook running actively

I'm absollutely new to VB so do bear w/ my simplest of questions.

TIA

jamus

 

[Zeroanarchy]

Jamus the question really is, is the computer left on?? if so you could write a bit of code that Automaticly runs outlook at a specfic time. ie 8am and since outlook is open it will send the required outbox mail.

Hope that helps

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[jamus]

Hi,

I'm planning to run it on one of my Servers (NT4 SP 6), I've in fact installed Excel and Outlook as a service but while Excel's Macro runs OK, Outlook hung.

just to be clear, Server is on but logged off, XL and OL running as separate services, XL & OL triggered to run at 6am by Scheduler. XL ran as planned but OL didn't, so no outgoing email :-(

Will OL still launch from via VB in this set up? I'm game to try but could you help me with the code to automatically run OL or where I could read up on VB coding?

TIA

jamus