creat csv of a tab and email

[baldwinp]

Hi all,

I have a file that I run my macro in and it creates a report on a particular sheet. I was wondering if it is possible to have a macro that will take this sheet that I have make a copy of it in a new file as a csv. Bonus if i can have that file emailed to someone. Maybe have a place on another tab where I can input a name and then hit a button and then it will convert output tab to csv and email it. Hope that is not too confusing.


Thanks in advance!

 

[Blade Hunter]

This should work:

Private Sub SaveCopy()
dim ArchiveFolder as string, ArchiveFileName  as string
ArchiveFolder = "G:\WhereYouWantToSaveTheFiles\" 'Change this
ArchiveFileName = "YourFileName - " & Format(Now - 1, "YYYYMMDD") & ".csv"
Range("A1").Select
Activesheet.copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=ArchiveFolder & ArchiveFileName, FileFormat:=xlCSV
Application.DisplayAlerts = True
ActiveWindow.Close
EmailCopy
End Sub




Private Sub EmailCopy()
Dim oApp, oMail As Object
Dim WB As Workbook
Dim FileName As String, BodyText As String
    Application.ScreenUpdating = False
    FileName = ArchiveFolder & ArchiveFileName
    On Error Resume Next
    On Error GoTo 0
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    With oMail
        BodyText = "Please find attached the Your Report Name Report for close of business " & Format(Now - 1, "DD/MM/YYYY") & "." & Chr(13) & Chr(13)
        BodyText = BodyText & "If there are any questions or concerns with the data please do not hesistate to contact me." 'Change this if you like
        BodyText = BodyText & .Body 'This is in there to retain your signature
        .To = "Person1@Email.com.au;Person2@email.com.au" 'Change this
        .Subject = Your Report Name Report for COB " & Format(Now - 1, "DD/MM/YYYY")
        .Body = BodyText
        .Attachments.Add FileName
        .Display
        .Send
    End With
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
End Sub

here I have now-1 I am removing 1 from today, the reason for this is I use this routine to send out my daily sales reports @ COB yesterday, you can remove the -1 if this is more relevant to you.

 

[Blade Hunter]

Ran out of edit time, move this outside the sub so the bottom routine can use it:

dim ArchiveFolder as string, ArchiveFileName as string

Or put it inside the call

 

[baldwinp]

Thank you for you help, I am not quite sure what your last comment was telling me to do. I tried to fix to know succes, I was able to get it to just save a file, but could not figure out the email part.

 

[Blade Hunter]

I meant like this

dim ArchiveFolder as string, ArchiveFileName  as string 'I moved this part up here


Private Sub SaveCopy()
ArchiveFolder = "G:\WhereYouWantToSaveTheFiles\" 'Change this
ArchiveFileName = "YourFileName - " & Format(Now - 1, "YYYYMMDD") & ".csv"
Range("A1").Select
Activesheet.copy
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=ArchiveFolder & ArchiveFileName, FileFormat:=xlCSV
Application.DisplayAlerts = True
ActiveWindow.Close
EmailCopy
End Sub








Private Sub EmailCopy()
Dim oApp, oMail As Object
Dim WB As Workbook
Dim FileName As String, BodyText As String
    Application.ScreenUpdating = False
    FileName = ArchiveFolder & ArchiveFileName
    On Error Resume Next
    On Error GoTo 0
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    With oMail
        BodyText = "Please find attached the Your Report Name Report for close of business " & Format(Now - 1, "DD/MM/YYYY") & "." & Chr(13) & Chr(13)
        BodyText = BodyText & "If there are any questions or concerns with the data please do not hesistate to contact me." 'Change this if you like
        BodyText = BodyText & .Body 'This is in there to retain your signature
        .To = "Person1@Email.com.au;Person2@email.com.au" 'Change this
        .Subject = Your Report Name Report for COB " & Format(Now - 1, "DD/MM/YYYY")
        .Body = BodyText
        .Attachments.Add FileName
        .Display
        .Send
    End With
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing
End Sub

The purpose of moving it out of the sub is to let the variables be used by other subs (ie the email one)