Email worksheet as TXT or as CSV

[Anne Troy]

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]

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]

{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]

Hee hee...
Okay. I'll get that code into that workbook and see what's what.

XXX
OOO

[Jacob Hilderbrand]

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]

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]

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]

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]

Anne

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

[Anne Troy]

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?