Excel send sheet as email attachment in outlook 1

[wwgmr]

Hi all,

I am trying to write a macro sub that will use named ranges from my sheet as values in the subject lines and other places. I seem to be having trouble with the code suggested in 2002 excel book and also in this forum. Here is what I have so far with some Rem lines showing variations I have tried. Any help is welcomed. I seem to be missing something here. I have tried activating the sheet to allow the macro to call the values but that gives me error when I walk through the code.

Sub SendEmail()

' Application.ActiveWorkbook.Worksheets("Request&quot.Activate
ESubject = "Request for " & ActiveWorkbook.Names(LastName).Value & ", " & ActiveWorkbook.Names(FirstName).Value & " " & ActiveWorkbook.Names(GroupName).Value
' ESubject = "Request for " & Range("Firstname&quot.Value
SendTo = Range(SupName).Value & "@mycompany.com"
Ebody = "Testing VBA's ability to send an email."
NewFileName = "C:\My Documents\TestFile.xls"

Set App = CreateObject("Outlook.Application&quot
Set Itm = App.CreateItem(0)
With Itm
.Subject = ESubject
.To = SendTo
.Body = Ebody
'.Attachments.Add (NewFileName) ' Must be complete path
'.Display ' This property is used when you want
' the user to see email and manually send. Then
' comment out rest of code except “End With” statement
' and "End Sub" statement.
.send
End With
Set App = Nothing
Set Itm = Nothing
End Sub

 

[VBAjedi]

Hi!

That looks like the code from my FAQ . . . it works well for me (XL97/Outlook 98). What exactly is happening? When you run the code, are you getting errors, or is nothing happening at all?

Have you tried testing the contents of your variables to make sure they contain what you expect? Add something like:

Debug.Print Esubject
Debug.Print SendTo
Debug.Print Ebody
Debug.Print NewFileName

before the "Set app" line. Run the code, and make sure what comes up in the VBA immediate window is what you expect. You may need to change how you refer to your named ranges. As long as the named ranges are in the same workbook as the code that refers to them, and as long as you aren't manipulating other workbooks with the same code, you usually don't need to explicitly reference the workbook. I usually use the form:

x = Sheets("MySheetName&quot.Range("MyRangeName&quot.Value

Also, I noticed that, while you assign an attachment file path to NewFileName, you aren't currently attaching it to the message. Was that just for debugging purposes?



VBAjedi

 

[SkipVought]

Hi,

You're ALMOST there.

Try putting QUOTES around your range names...

    ESubject = "Request for " & ActiveWorkbook.Names("LastName").Value & ", " & _
        ActiveWorkbook.Names("FirstName").Value & " " & _
        ActiveWorkbook.Names("GroupName").Value
    ' ESubject = "Request for " & Range("Firstname").Value
    SendTo = Range("SupName").Value & "@mycompany.com"

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[wwgmr]

Hi VBAjedi

I found that I had made mistake I needed to Dim the fields as String and then just use Range("FirstName&quot.value

This part worked. What I am stuck on now is making sure it sends the active worksheet as an attachment? What would be teh code for that?

Would I use it in the .Ebody?

Thanks!

 

[SkipVought]

I replied too soon.

use this simplified range assignment...

    ESubject = "Request for " & [LastName] & ", " & _
        [FirstName] & " " & _
        [GroupName]
    ' ESubject = "Request for " & Range("Firstname").Value
    SendTo = [SupName] & "@mycompany.com"

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[wwgmr]

Thank you Skip that is much cleaner. Since I am calling this from a worksheet it should then attach the file or do I need to add code for that?

 

[VBAjedi]

As long as you haven't made any changes since you last saved, you can just point to the path where the active workbook is saved (use the code that's commented out), and it will attach it.

If you have made changes you want to include, call ActiveWorkbook.Save first.

VBAjedi

 

[SkipVought]

Just add

        .Attachments.Add ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[wwgmr]

I cann't use the path as it's going to be a template file that will be blank when they start and cannot be saved. Could I prompt them to save local and capture where they saved it to use? What code would that be?

 

[SkipVought]

    With Itm
        .Subject = ESubject
        .To = SendTo
        .Body = Ebody
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs "C:\Temp\Temp.xls"
        .Attachments.Add ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
        Application.DisplayAlerts = True
        .send
    End With

Just save it to the temp folder

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[wwgmr]

Perfect Skip Thanks! Someday I might get really good at this VBA,

Thanks both of you.

 

[wwgmr]

I thought I had it yet when I put in the Call Sendemail in the click event from a Form I have I get error Compile error:Ambiguous name detected: SendEmail ??

Any Idea? Do I need to select the sheet before calling?

 

[SkipVought]

Make sure that SendEmail is

in a MODULE,

NOT in a sheet object

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VBAjedi]

Sounds like you may have two subs with the same name.

As an aside: I personally prefer to avoid naming my subs really obvious things like "SendEmail" because it's too easy to accidentally use a (current or future) reserved word. Instead, I use something like "EmailFileAsAttach". . .

VBAjedi

 

[wwgmr]

I was able to clear that up the only problem I seem to be having now is that we have user rights and saving to c: drive is restricted save under the users name. So I am going to need to either prompt them to save right before I start this command or write something to capture there loggin name and then add it to the "c:\Documents and settings\" & Username.

Does this sound right?
I tried to use the activeworkbook.saveas to prompt but that doesn't seem to do what I need it to.

lol seemed so simple at first. I do feel close to having it finished.

 

[SkipVought]

Why won't SaveAS work with DisplayAlerts=FALSE?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[wwgmr]

The file is going to be kept on secure folder. They will not have rights to save to that location. They will have to save it local. When I tried just putting in the saveas it doesn't seem to popup with the save as Window. ?
If I can have it give them choice they can save it local on their drive.

 

[wwgmr]

I also wanted to check why would I get a runtime error 438 object doesn't suppor this property or method when I try to check if a named range is "" then if it is I want to hide command button using cmbMyButton.visable = false
? I am putting this in the Worksheet.activate code. So it can check before doing anything else.

I seem to be still having trouble knowing how to refer to objects and ranges. Going back to reread the begining of my book again.

Any help on this and the last part of my email problem is welcomed thanks!

 

[SkipVought]

Well didn't I have a path to the Temp folder in the saveas?

Why won't

ActiveWorkbook.SaveAs "C:\Temp\Temp.xls"

work?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[wwgmr]

We don't have rights to save to the c: drive for what ever reason. So I cannot have it write to either a temp folder or even the winnt\temp folder access is denied.

The only way would be to capture the users name then set that as a string and add it to something like
activeworkbook.SaveAs "C:\documents and settings\" & username & "\Temp.xls"