-
Feb 11th, 2012, 06:55 PM
#1
Thread Starter
New Member
[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:
- Extract that single active worksheet to a new workbook.
- 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.
- Open a new Outlook message.
- Populate the To:, Subject:, and Body with my own addresses and text.
- Attach the recently saved workbook.
- And then finally display the message and not send it. I believe this will use .display instead of .send in the code.
- 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
-
Feb 13th, 2012, 07:02 AM
#2
Thread Starter
New Member
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
-
Feb 13th, 2012, 03:28 PM
#3
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
-
Feb 13th, 2012, 03:33 PM
#4
Thread Starter
New Member
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.
-
Feb 13th, 2012, 03:37 PM
#5
Frenzied Member
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
-
Feb 13th, 2012, 03:45 PM
#6
Frenzied Member
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!
-
Feb 13th, 2012, 04:54 PM
#7
Thread Starter
New Member
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.
-
Feb 13th, 2012, 05:05 PM
#8
Frenzied Member
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!
-
Feb 13th, 2012, 06:47 PM
#9
Thread Starter
New Member
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.
-
Feb 14th, 2012, 06:44 AM
#10
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
-
Feb 14th, 2012, 08:49 AM
#11
Thread Starter
New Member
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.
-
Feb 14th, 2012, 08:54 AM
#12
Frenzied Member
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!
-
Feb 14th, 2012, 08:56 AM
#13
Frenzied Member
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!
-
Feb 14th, 2012, 05:46 PM
#14
Thread Starter
New Member
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.
-
Feb 17th, 2012, 04:30 PM
#15
Frenzied Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|