asked on

Auto email an attachment

To the VB Expert

I have what i believe to be a small problem.

I have created a small Utilities that collect Numbers to send via e-mail. It goes as followed

1.- the utility collect info on some text files then create a vbscript:

VB Script sample:
---------------------------------------------
Option Explicit
Dim Row, XL, WshShell, FileSystem, RegularExpression, Dummy, TheNVFile, TheLine
Dim Whacks, WhacksFound, WhacksPattern, Flag, Location
Dim TheDate, Filename, Book
Const ForReading = 1
Row = 2
Set XL = WScript.CreateObject("Excel.Application")
Set WshShell = WScript.CreateObject("WScript.Shell")
Set FileSystem = CreateObject("Scripting.FileSystemObject")
Set RegularExpression = New RegExp
Dummy = WshShell.Popup ("Collecting info. Please Wait...",1,"LMC Flash Utility",64)
Call BuildSpreadSheet()
Call SaveSpreadSheet()
Wscript.Quit
Sub BuildSpreadSheet()
Set Book = XL.WorkBooks.Add
XL.Columns(1).ColumnWidth = 12
XL.Columns(2).ColumnWidth = 12
XL.Columns(3).ColumnWidth = 12
XL.Columns(4).Columnwidth = 12
XL.Columns(5).Columnwidth = 12
XL.Columns(6).Columnwidth = 12
XL.Columns(7).Columnwidth = 12
XL.Columns(8).Columnwidth = 12
XL.Cells(1, 1).Value = "Location"
XL.Cells(1, 2).Value = "Count"
XL.Cells(1, 3).Value = "CSI"
XL.Cells(1, 4).Value = "Man Hours"
XL.Cells(1, 5).Value = "V/Hour"
XL.Cells(1, 6).Value = "Total Disc"
XL.Cells(1, 7).Value = "AVG Disc"
XL.Cells(1, 8).Value = "Report Time"
XL.Cells(3, 1).Value = "SITE0001"
XL.Cells(3, 2).Value = "45"
XL.Cells(3, 3).Value = "$65.78"
XL.Cells(3, 4).Value = "35"
XL.Cells(3, 5).Value = "12"
XL.Cells(3, 6).Value = "$300.60"
XL.Cells(3, 7).Value = "$3.05"
XL.Cells(3, 8).Value = "9:00 PM"

You get the idea similar numbers up to row 22

XL.Range("A1:A25").Select
XL.Selection.Font.Bold = True
XL.Selection.Font.Size = 10
XL.Cells(26, 1).Value = "Total"
XL.Cells(26, 2).Value = "=SUM(B3:B24)"
XL.Cells(26,3).Value = "=(B3*C3+B4*C4+B5*C5+B6*C6+B7*C7+B8*C8+B9*C9+B10*C10+B11*C11+B12*C12+B13*C13+B14*C14+B15*C15+B16*C16+B17*C17+B18*C18+B19*C19+B20*C20+B21*C21+B22*C22+B23*C23+B24*C24)/B26"
XL.Cells(26, 4).Value = "=SUM(D3:D24)"
XL.Cells(26, 5).Value = "=SUM(E3:E24)"
XL.Cells(26, 6).Value = "=SUM(f3:f24)"
XL.Cells(26, 7).Value = "=SUM(g3:g24)"
XL.Range("A1:h1").Select
XL.Selection.Font.Bold = True
XL.Selection.Font.Size = 10
XL.Range("A26:h26").Select
XL.Selection.Font.Bold = True
XL.Selection.Font.Size = 10
End Sub
Sub SaveSpreadSheet()
TheDate = Date
TheDate = Replace(TheDate, "/", "-")
FileName = "D:\Report.xls"
Book.SaveAs(FileName)
End Sub

2.- the utility call the VB script.

This is the problems that I am having:

I want to overwrite any existing reports.xls without any prompt.

I need to automatically e-mail the file Report.xls via SMTP or Exchange.

I am not a VB expert. So please try to make your answer explicit. As a matter of fact it took me weeks to make the above.

Thanks in advance for your help

AzraSound

For emailing you can use this free component:
http://www.freevbcode.com/ShowCode.Asp?ID=109

jimbobmcgee

A simple way to remove the prompt is to ensure there is no file before you save, by deleting the file:

...
FileName = "D:\Report.xls"
If FileSystem.FileExists(FileName) Then FileSystem.DeleteFile(FileName)
Book.SaveAs(FileName)
...

If you can access an Outlook application class, like you do with Excel, you might be able to send your mail via Outlook:

Dim myOL, myMail, ShowMailBeforeSend

ShowMailBeforeSend = True

Set myOL = CreateObject("Outlook.Application")
Set myMail = myOL.CreateItem(olMailItem)

myMail.SenderName = "Automated emailer"
myMail.To = "somebody@somedomain.com"
myMail.Subject = "Report"
myMail.Attachments.Add (Filename)

If ShowMailBeforeSend = True Then
myMail.display
Else
myMail.Send
End If

HTH

J.

Murat Raymond

ASKER

Thanks Jimbobmcgee I will try and let you know.

Murat Raymond

ASKER

Thanks Jimbobmcgee

It didn't work It gave me an error on line 5, Char 8
it also said that the file is read only, which I check but is not.

Murat Raymond

ASKER

Jimbobmcgee

The prompt suggestion works. However I am still not able to send the email.

Mail be you forgot to add something or I am doing something wrong. please help.

Murat Raymond

ASKER

I have found a script that does what i needed. However I cannot add an attachment to it:

This is the script:

Set objEmail = CreateObject("CDO.Message")
objEmail.From = "monitor@domain.com"
objEmail.To = "user@domain.com"
objEmail.Subject = "Email test"
objEmail.Textbody = "How do I attach a file to this email."
objEmail.Send

How do I attach a file like "report.txt" to this script

ASKER CERTIFIED SOLUTION

jimbobmcgee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Murat Raymond

ASKER

I would like to use CDO. However by adding : objEmail.AddAttachment("report.txt")

I get the error:

Line: 6
Char: 1
error: The specified protocol is unknown
code: 800C00d
source: CDO.Message.1

Any Suggestion?

jimbobmcgee

Try using the full path instead of just "report.txt", e.g. "c:\report.txt".

See http://systemwebmail.com/faq/4.3.7.aspx for details...

J.

Murat Raymond

ASKER

It worked Thank you.

Murat Raymond

ASKER

If I wanted to write those scripts to an existing excel file how would I do That?

Note: either way you deserve the 500

Murat Raymond

ASKER

Never mind I got it