Results 1 to 15 of 15

Thread: [RESOLVED] [Excel] Help with saving and emailing worksheet

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    15

    Resolved [RESOLVED] [Excel] Help with saving and emailing worksheet

    Hello All,

    Thank you for taking time to help me out. I am new to VBA (hint: my username) and am constantly learning new code and their functions. I am to the point where I can take some already written code, fumble through it, and manipulate it to function as I need it. The situation that I'm in now, I have not been able to find exactly what I need.

    As I've seen posted in a lot of areas, you can easily take an active worksheet, temporarily save it, open an Outlook message, attach the file, and then send it. After all is done, the code deletes the temp file.

    I have a workbook with about 70 worksheets. I want to put one button on each worksheet that will:

    1. Extract that single active worksheet to a new workbook.
    2. Save the new workbook to a specified network drive using the sheet name + today's date as the file name. Example: Access Control 02/11/2012.xlsx.
    3. Open a new Outlook message.
    4. Populate the To:, Subject:, and Body with my own addresses and text.
    5. Attach the recently saved workbook.
    6. And then finally display the message and not send it. I believe this will use .display instead of .send in the code.
    7. Somewhere in here too, when done attaching the file, close the new workbook.


    The code listed below partially works for me, but I'm sure there are things in there that I don't need, and probably a few extra things that I need.

    Code:
    Sub Mail_ActiveSheet()
    'Working in 2000-2010
        Dim FileExtStr As String
        Dim FileFormatNum As Long
        Dim Sourcewb As Workbook
        Dim Destwb As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
        Dim OutApp As Object
        Dim OutMail As Object
    
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
        Set Sourcewb = ActiveWorkbook
    
        'Copy the sheet to a new workbook
        ActiveSheet.Copy
        Set Destwb = ActiveWorkbook
    
        'Determine the Excel version and file extension/format
        With Destwb
            If Val(Application.Version) < 12 Then
                'You use Excel 2000-2003
                FileExtStr = ".xls": FileFormatNum = -4143
            Else
                'You use Excel 2007-2010, we exit the sub when your answer is
                'NO in the security dialog that you only see  when you copy
                'an sheet from a xlsm file with macro's disabled.
                If Sourcewb.Name = .Name Then
                    With Application
                        .ScreenUpdating = True
                        .EnableEvents = True
                    End With
                    MsgBox "Your answer is NO in the security dialog"
                    Exit Sub
                Else
                    Select Case Sourcewb.FileFormat
                    Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
                    Case 52:
                        If .HasVBProject Then
                            FileExtStr = ".xlsm": FileFormatNum = 52
                        Else
                            FileExtStr = ".xlsx": FileFormatNum = 51
                        End If
                    Case 56: FileExtStr = ".xls": FileFormatNum = 56
                    Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
                    End Select
                End If
            End If
        End With
    
        '    'Change all cells in the worksheet to values if you want
          '  With Destwb.Sheets(1).UsedRange
          '      .Cells.Copy
          '     .Cells.PasteSpecial xlPasteValues
          '      .Cells(1).Select
          '  End With
          '   Application.CutCopyMode = False
    
        'Save the new workbook/Mail it/Delete it
        TempFilePath = "Z:\ABC\ABC Terminal\ASOC\ABC Matrix Reports"
        TempFileName = ActiveSheet.Name & " " & Format(Date, "mm-dd-yyyy")
    
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
        With Destwb
            .SaveAs TempFileName & FileExtStr, _
                    FileFormat:=FileFormatNum
            On Error Resume Next
            With OutMail
                .To = "abc123@abc.com"
                .CC = ""
                .BCC = ""
                .Subject = "ABC Matrix Report " & Format(Date, "mm/dd/yyyy")
                .body = ""
                .Attachments.Add Destwb.FullName
                'You can add other files also like this
                '.Attachments.Add ("C:\test.txt")
                .display   'or use .Display
            End With
            On Error Resume Next
            .Close SaveChanges:=True
        End With
    
        
        Set OutMail = Nothing
        Set OutApp = Nothing
    
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub
    Also, when I am writing text in the body of the email, how would I go about making a particular section of text red?
    Code:
    .body = "How would I make this string text red?"
    Thanks again for any help provided. I continue to research and learn more as I go along and hope for this to be a great learning experience.

    -Adam

  2. #2

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    15

    Re: [Excel] Help with saving and emailing worksheet

    I see a lot of people looking, but no help yet . Is there any more information that I can provide that will help out? Thanks again.

    Adam

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [Excel] Help with saving and emailing worksheet

    what problems are you having with this code?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    15

    Re: [Excel] Help with saving and emailing worksheet

    The file is not saving in the specified location, it seems to default to My Documents. I would also like for the newly created workbook to close after it's attached to the email. And I'm not sure how to change the color of the text in the email body to red. Thanks for your response.

  5. #5
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: [Excel] Help with saving and emailing worksheet

    I dont know where you got the code from but it is way too generic and is handeling a load of stuff you don't need ( i think)

    all of what you want can be captured in a recorded macro and then enhanced..

    that way you would learn about how and why your solution worked and could manage it in the future

    here to suggest

  6. #6
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: [Excel] Help with saving and emailing worksheet

    I just had a quick squint over the code, not sure i like all i see, but what i do see is a "c:\test.txt" file and not the file you built being attached!

    could this be your simple error

    just a thought

    I would still want to make this from scratch just for your problem!

  7. #7

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    15

    Re: [Excel] Help with saving and emailing worksheet

    That is what I was afraid of, too much going on in the code for what I need. That's why I have come to the experts who can guide me in the right direction. I found this code online, and just tried to modify it to what I needed. That is the case and point for more code than what I need it for.

    Sorry for not color coding it different, but that c:\test.txt is not actually built in to the code, it is just an explanation of how you could attach another file. Once again, that came already in the code.

  8. #8
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: [Excel] Help with saving and emailing worksheet

    oops i missed the apostrophies

    have you tried to record the process using record macro?

    there is a direct mail process build into vba as well i think

    way too much stuff to remember!

  9. #9

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    15

    Re: [Excel] Help with saving and emailing worksheet

    I started to try recording a macro, but I am not familiar with the built in mail process.

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [Excel] Help with saving and emailing worksheet

    The file is not saving in the specified location, it seems to default to My Documents.
    while you have a variable for the file path you are not using that when saving the file, hence it is going to the default folder location

    I would also like for the newly created workbook to close after it's attached to the email.
    as there is a .close statement i am not sure why it is not closing unless some error is occurring before that line, remove all instances of on error resume next, so if some error occurs you will get a break

    i do not use outlook, so have little idea about how to set some text to red, i assume you need to use .htmlbody and use html coding to colour the text as required, but there may be other ways as well
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    15

    Re: [Excel] Help with saving and emailing worksheet

    while you have a variable for the file path you are not using that when saving the file, hence it is going to the default folder location
    I see what you are saying here. When is goes to the .SaveAs line, of course its referencing Destwb but not the FilePath. How do I go about adding that reference?

    as there is a .close statement i am not sure why it is not closing unless some error is occurring before that line, remove all instances of on error resume next, so if some error occurs you will get a break
    I did remove all instances of on error resume next, and the document does close, and there are no errors while the code runs. That was my mistake, it does close.

    What is the easiest way to change the .body format to .htmlbody? Is it as simple as that, just replace .body with .htmlbody?

    I'm almost there guys, thanks for the help.

  12. #12
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: [Excel] Help with saving and emailing worksheet

    place the path in this line and do not forget the /
    Code:
           .SaveAs TempFileName & FileExtStr, _
                    FileFormat:=FileFormatNum
    to
    Code:
           .SaveAs TempFilePath & "\" & TempFileName & FileExtStr, _
                    FileFormat:=FileFormatNum
    you could of course add the \ to the path and just use "Path" & "name" instead of "path" & "\" &"name"

    hope that helps

    still way too much stuff in the code though!

  13. #13
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: [Excel] Help with saving and emailing worksheet

    BTW is the "abc report" the same as the name of the sheet?

    because you can automate the sheet harvesting as well!

  14. #14

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    15

    Re: [Excel] Help with saving and emailing worksheet

    BTW is the "abc report" the same as the name of the sheet?
    because you can automate the sheet harvesting as well!
    No, ABC Matrix Report is just the folder that I created to put all the new saved workbooks into. Each sheet has a different name.

    Using:
    Code:
    TempFileName = ActiveSheet.Name & " " & Format(Date, "mm-dd-yyyy")
    has been doing a pretty good job at getting me the file name I need.

  15. #15
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: [Excel] Help with saving and emailing worksheet

    sorry for late reply...

    the problem of the default saving directory is because you are using just the tempfilename and not the tempdirectory & "\" & tempfilename when you are using the .saveas stuff in your code

    here to help

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width