[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 = "[email protected]"
            .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

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

what problems are you having with this code?

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.

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

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!

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.

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!

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

Quote:

---

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

Quote:

---

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

Quote:

---

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?

Quote:

---

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. :D

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!

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

because you can automate the sheet harvesting as well!

Quote:

---

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.:thumb:

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