PDA

View Full Version : Solved: Email worksheet as TXT or as CSV



Anne Troy
08-16-2004, 10:46 PM
See the DataSheet.
I want to be able to put as many email addresses I want in that column, tho it will likely only be 3 or 4 max.

Then, on hitting the submit timesheet menu option, it will save the TimeSheet worksheet as a TXT or CSV (don't really care which--whichever is most common or flexible) and email it to those email addresses. It can temporarily save the txt/csv to the %TEMP% folder, but should then be deleted.

In this particular case, the email client is Groupwise. Yuk! I know...

We'll need the Emp ID number to be inserted as Column A in the TXT or CSV, the Month inserted as Column B. These can be there and just hidden if you want to do it before export.

Do y'all like my code? That's the extent of it. But you guys ARE teaching me. Jake: I finally got the msgbox thing down 'cause of YOU.

Hopeful future of this project:
File is received through email program from 90 employees and either:
--imported into an Excel file
--imported into an Access DB (my choice, but not the user's)

Anyway, I think this could be a cool tool from the coders of VBAX till it's all said and done...this one only reports days out, but we could make one that reports all...

Jacob Hilderbrand
08-16-2004, 11:29 PM
What is Groupwise? They really need to use Outlook. :*)

This code will export the sheet, save it, then delete it. I just made the path c:\ but that can be changed to any valid path.


Option Explicit

Sub Export()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Sheets("TimeSheet").Copy
ActiveWorkbook.SaveAs Filename:="C:\Time Sheet.txt", FileFormat:=xlText
ActiveWorkbook.Close

'Code here for emailing the file.

Kill "C:\Time Sheet.txt"

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Jacob Hilderbrand
08-16-2004, 11:32 PM
{Snip} Jake: I finally got the msgbox thing down 'cause of YOU. {Snip}

By the time I am done you won't be able to say you're not a coder anymore. :)

Anne Troy
08-16-2004, 11:37 PM
Hee hee...
Okay. I'll get that code into that workbook and see what's what. :)

XXX
OOO

Jacob Hilderbrand
08-17-2004, 12:20 AM
No Body Text with sendmail, but you can't have everything.


Option Explicit

Sub Export()

Dim Recipients() As String
Dim x As Long
Dim LastRow As Long

Application.DisplayAlerts = False
Application.ScreenUpdating = False

LastRow = Sheets("DataSheet").Range("F65536").End(xlUp).Row
ReDim Recipients(1 To LastRow - 1)
For x = 2 To LastRow
Recipients(x - 1) = Sheets("DataSheet").Range("F" & x).Text
Next x
Sheets("TimeSheet").Copy
ActiveWorkbook.SaveAs Filename:="C:\Time Sheet.txt", FileFormat:=xlText
ActiveWorkbook.SendMail Recipients, "Subject"
ActiveWorkbook.Close
Kill "C:\Time Sheet.txt"

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Anne Troy
08-17-2004, 07:25 AM
EXCELLENT! I got your sample.
What we need to change:

I don't need row 1 or row 2; just need the data part
We DO need column A to have the emp ID, and column B to have the Month/Year.

Of course, we can mark this solved, and I'll open a new Q if you like. :)

Jacob Hilderbrand
08-17-2004, 04:26 PM
Where do I find the emp ID? This will delete rows 1:2 and insert a column at A so the date in in column B.


Option Explicit

Sub Export()

Dim Recipients() As String
Dim x As Long
Dim LastRow As Long

Application.DisplayAlerts = False
Application.ScreenUpdating = False

LastRow = Sheets("DataSheet").Range("F65536").End(xlUp).Row
ReDim Recipients(1 To LastRow - 1)
For x = 2 To LastRow
Recipients(x - 1) = Sheets("DataSheet").Range("F" & x).Text
Next x
Sheets("TimeSheet").Copy
Range("1:2").Delete
Range("A:A").Insert
ActiveWorkbook.SaveAs Filename:="C:\Time Sheet.txt", FileFormat:=xlText
ActiveWorkbook.SendMail Recipients, "Subject"
ActiveWorkbook.Close
Kill "C:\Time Sheet.txt"

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

jamescol
08-17-2004, 09:09 PM
Anne,
This sample is (so far) untested, but should enable you to send a MAPI email via Groupwise as long as GW is the default mail program. Actually, this sample will simply send a message via the default MAPI email client - really doesn't matter which one is being used. Good to keep in your back pocket.

James


Sub SendMAPIMail()
Dim objSession As Object
Dim objMessage As Object
Dim objRecipient As Object
Dim strSubject as String 'From Excel data
Dim strMessageBody as String 'From Excel data
Dim strRecipient as String 'From Excel


' Create the Session Object
Set objSession = CreateObject("MAPI.Session")


' Log on using the session object
objSession.Logon , , False, False


' Add a new message to the OutBox
Set objMessage = objSession.Outbox.Messages.Add


'Set the mail item properties
objMessage.Subject = strSubject
objMessage.Text = strMessageBody


'Add a recipient object to the objMessage.Recipients collection
Set objRecipient = objMessage.Recipients.Add


' Set the properties of the recipient object
objRecipient.Name = strRecipient
objRecipient.Resolve


'Send the message
objMessage.Send showDialog:=False


' Log off using the session object
objSession.Logoff


'Cleanup
Set objSession = Nothing
Set objMessage = Nothing
Set objRecipient = Nothing

Jacob Hilderbrand
08-20-2004, 07:18 AM
Anne

Can we mark this one solved, or does it still need some more work?

Anne Troy
08-20-2004, 07:25 AM
I don't know.
The guy never got back to me, and I'm hesitant to chase him...
BUT, it's good stuff.
Can we add any of it to the KB?

I think mark it solved, huh?