Accessing data from remote computer

[bigbuckaroo]

Greetings all,

This is really my first attempt at using VBA so please be kind and if more information is needed let me know and I will try to provide it.

I have a computer that is seperate from the company IT network and connected to our production floor. Call this Production Server computer. This computer is connected to various prodction machines to collect data, anounce alarms and such. The software running on the computer to do this is Allen Bradley RSVIEW 32 if that makes a difference. Every day there are numerous files generated by the software that are of the DBF type. This is the only file type that is available. The computer also has Microsoft Outlook installed.

What I would like to do is use a VBA routine in conjunction with the data collection software and Outlook to email myself a copy of the saved DBF files maybe as an attatchment. Then I would like to use Excel on my computer to sort the data and such. Basically the initial problem is I do not know how to program VBA to send an attachemtn via email. I also have installed on this computer the Outlook Redemption software to bypass the seceurity warnings in Outlook.

Can anyone provide an example or a nudge in the right direction?

Thanks

Tom

 

[PHV]

Have a look here:
thread705-1369407

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bigbuckaroo]

I really do apologise as I am really new to this VBA stuff.

Here is a copy of what I have so far:



Sub try_it()

Set App = CreateObject("Outlook.Application")

Set NameSpace = App.GetNamespace("MAPI")

NameSpace.Logon
Dim SafeItem, oItem
Set SafeItem = CreateObject("Redemption.SafeMailItem") 'Create an instance of Redemption.SafeMailItem
Set oItem = App.CreateItem(0) 'Create a new message
SafeItem.Item = oItem 'set Item property
SafeItem.Recipients.Add "tbiel@schoeps.us"
SafeItem.Recipients.ResolveAll
Dim attachment As SafeItems

Dim sfle As String, sfol As String, dfol As String, afile As String, bfile As attachment

sfle = "2010 09 19 0000 (Float).DBF" ' change to match the file name
sfol = "D:\Product_Counts\Product Counts\" ' change to match the source folder path
dfol = "D:\counter storage\" ' change to match the destination folder path

Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(sfol & sfle) Then
MsgBox sfol & sfle & " does not exist!", vbExclamation, "Source File Missing"
ElseIf Not fso.FileExists(dfol & file) Then
fso.CopyFile (sfol & sfle), dfol, True

Else
MsgBox dfol & file & " already exists!", vbExclamation, "Destination File Exists"
End If
SafeItems = ("dfol & sfle")
attachment.Add SafeItems

SafeItem.Send


End Sub

I keep getting the following error "run time error 91 object variable or with block variable not set" This occurs in the line: attachment.add safeitems.

As you can probably see I have been trying to piece together code found via internet search. Can Someone please take a look at this and let me know what is wrong?

Thanks

 

[bigbuckaroo]

Ok here is what I have pieced together so far:



Sub try_it_3()

Set App = CreateObject("Outlook.Application")

Set NameSpace = App.GetNamespace("MAPI")

NameSpace.Logon
Dim SafeItem, oItem
Set SafeItem = CreateObject("Redemption.SafeMailItem") 'Create an instance of Redemption.SafeMailItem
Set oItem = App.CreateItem(0) 'Create a new message
SafeItem.Item = oItem 'set Item property
SafeItem.Recipients.add "xxx@yyyy.com"
SafeItem.Recipients.ResolveAll

curdate = Date

Dim oFS As Object

Set oFS = CreateObject("Scripting.FileSystemObject")



Dim sfle As String, sfol As String, dfol As String, afile As String

sfol = "D:\Product_Counts\Product Counts\" ' change to match the source folder path

sfle = "2010 09 27 0000 (Wide).DBF" ' change to match the file name

dfol = "D:\counter storage\" ' change to match the destination folder path

If Not oFS.FileExists(sfol & sfle) Then
MsgBox sfol & sfle & " does not exist!", vbExclamation, "Source File Missing"
ElseIf Not oFS.FileExists(dfol & sfle) Then
oFS.CopyFile (sfol & sfle), dfol, True
Else
MsgBox dfol & sfle & " already exists!", vbExclamation, "Destination File Exists"
End If

Set attach = SafeItem.Attachments.add(dfol & sfle)

SafeItem.Send

End Sub


The code runs fine as is. It attaches the file to an email and sends it. Problem I am having now is I would like to set the "sfle" dynamically as a new file is created daily. I am unable to figure how to use the datecreated item under the file option of the scripting library. Can someone give me a little help? I apologise if I am not using the correct terminology as I am still new to the world of VBA

Thanks

Tom

 

[SkipVought]

'sfle = "2010 09 27 0000 (Wide).DBF" ' change to match the file name

sfle = [b]Format(Date, "yyyy mm dd") & "[/b] 0000 (Wide).DBF" ' change to match the file name



Skip,
[sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]

 

[bigbuckaroo]

Thanks for the quick reply. I am being called to another crisis so I will implement your suggestion later today.

Tom

 

[bigbuckaroo]

Worked awesome.

Thank you for your help.

Tom