PDA

View Full Version : Solved: Pls HELP!! w/ email a worksheet



joelle
04-29-2005, 10:52 AM
Dear Experts,
I saw loads of threads on emailing a ws but I still cannot solve my simple?? case.
I have a macro below to automatically attach a ws -- and a recipient -- to a new email window with the code below.

It brings up the email window ok and it DOES attach my ws but it doesnot attach the recipient???
Whats the flaw here. Please I also want the code to work independently form the user email.
Pls help.
Nee

=====================
Sub SendIt ()
Application.Dialogs(xlDialogSendMail).Show _
arg1:="abc@abc.com", _
End Sub
======================

Zack Barresse
04-29-2005, 11:14 AM
Hello Nee, and welcome to the board!!

(Are we allowed to speak your name? .. Monty Python joke..) This works for me ...

Sub SendIt()
Application.Dialogs(xlDialogSendMail).Show arg1:="abc@abc.com"
End Sub

What you have does not work for me because of the comma and underscore after the email address. That's a line continuation. Is that what you are talking about? If this doesn't work, tell us what email client you are using, version, etc.

joelle
04-29-2005, 11:24 AM
Hello Zack,

I'm soooo glad to see your quick writeback (very appreciative!)

however, the code still does not work 4 me after I revised it to:
==========================
Sub SendIt()
'
' SendIt Macro
' Macro recorded 4/28/2005 by Nee
'

'
Application.Dialogs(xlDialogSendMail).Show arg1:="abc@abc.com"
End Sub
==========================

I'm using Netscape 7.2 and I would like my code to be independent of the user email (= they should be able to click the button, see a popup mail window with the attachment and a recipient already filled in).
Again, I can see all of this except for the abc@abc.com from the "to:" line??

Please help. Thanks again.
Nee

EricM
04-29-2005, 11:29 AM
I have used this. I got this one from here and works great.



Option Explicit
Sub EmailWithOutlook()
'Variable declaration
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String

'Turn off screen updating
Application.ScreenUpdating = False

'Make a copy of the active sheet and save it to
'a temporary file
ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = "Temp.xls"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\" & FileName

'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
' .To = "me@here.com; me@somewhereelse.com"
'Uncomment the line below to hard code a subject
.Subject = "Nascar!"
.Attachments.Add WB.FullName
'Send it right away, use .Display to just show the mail
' Comment out below line and uncomment .send if you just want to send it
.display
'.send
End With

'Delete the temporary file
WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False

'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub

MOS MASTER
04-29-2005, 12:01 PM
Hi and welcome to VBAX! :hi:

"Nee" means "No" in Dutch so i'd love to know why you would choose it...:rofl:

Whanna send Email via Excel VBA..please Enter the arena off GrandMaster: Ron de Bruin and feast you're eyes on how many ways there are to frye this chicken: http://www.rondebruin.nl/sendmail.htm

Enjoy! :whistle:

joelle
04-29-2005, 01:33 PM
Hello Eric,
I'm so thankful to the codes you provided but somehow it does not work for me. Also, I dont need to save the file. I'd like to stick to my simple code and make the recipient included in the macro. As stated earlier, I would not want to have to rely on Outlook.
I heard if using RoutingSlip, the code will work independently from the user email. But it is too advanced for me to code.
Any other idea would be greatly appreciated.

=================
Now MosMaster --
In regards to my name, Nee or No, up to you. But instead of discussing about names, please post some tips on my question which sure is more appreciated.
Please like I begged before do not send links -- I read them before and they just make my head bigger. Are u still in the mood to help???
Nee

MOS MASTER
04-29-2005, 01:44 PM
Seams you don't like jokes...no problem! :rofl:

I've run the code you gave:
Sub SendIt()
Application.Dialogs(xlDialogSendMail).Show arg1:="abc@abc.com"
End Sub

It's run's fine over here so we have to find out why it won't work for you.

In witch version of Excel are you working?

lucas
04-29-2005, 02:06 PM
The code works fine if your using outlook. I use it in my personel.xls and set up one for each person I email workbooks to regularly, then set up a button for each person. works great. But...I don't think she wants to use outlook...I think its a little more difficult.

joelle
04-29-2005, 02:07 PM
Oh Joost - please do not get upset - but you're quite wrong! aside from excel tips, I DO love jokes.
Ok, to trade for the tip, here's answer about my name (I know others care less...) Its honestly "Nghi" but my coworkers dont know how to say it so I developed the habit of using Nee for posting -- nothing to hide actually.
In Dutch it means NO you said? not good!
In English it means KNEE - equally bad.

So, I need some sympathy here and please help me get my code work :bow:

I use Excel 2000. But my code below work well -- I just dont know how to add a recipient. Again, I dont know you but I like your joke now - so time to help me.

Nee
==========================
Sub email_myform()
'
' email_myform Macro
' Macro recorded 2/6/2003 by Nghi Truong
'

'
Application.Dialogs(xlDialogSendMail).Show
End Sub
============================

MOS MASTER
04-29-2005, 02:20 PM
Hi Nghi, :D

Nice to meet yah...and please..I don't get offended that easily! :rofl: (So forgive me as well)

Love to help you so I'll do my best.

The problem is I don't have Excel 2000 and it would be nice to know if some other 2000 user has the same problem with this function as you have. (My default is also Outlook so it seams like Lucas has a point there)

If so than it will get trickie to get this to work properly!

You talked about the hasroutingslip method but this is normaly used for sharing files with mulitple people and receive input from them.

If executed it send's the mail Imediatly so do Display method like the Dialog..(Don't know if that is a problem)

The other thing is that that email is part off a circulationlist and you'll be asked to send that sheet back to the person who has send it. (Can get irritating)
Here it is in any case:
Sub Test()
With ThisWorkbook
.HasRoutingSlip = True
With .RoutingSlip
.Delivery = xlAllAtOnce
.Recipients = "someone@vba.com"
.Subject = "Hi"
.Message = "Does this work for yah"
End With
.Route
End With
End Sub

Don't think it will be usefull though...

But I'll look and see if there is more to dig up for yah!

Can you tell me what you're default emailclient is?

Enjoy! :whistle:

joelle
04-29-2005, 02:31 PM
Hello Joost,

I'm glad you're an relaxed, easy guy!
Anyway, thank you for the codes. I plugged it in but it shows me an error msg:
"general mail failure... blah blah"
and the ".has routingslip = true" is hilighted.

I use Netscape email but my coworkers can use whatever they want and my code works for them. But my ambition is to add the recipient so when they use my macro they dont have to type in abc@abc.com (its a static address).

Any glue?
Nee

johnske
04-29-2005, 02:35 PM
Hi Nee,

This one requires you to have the email address(es) in the range A1 to A10 on the first sheet in the book (you can mail the same thing to up to 10 people at a time)Option Explicit
Sub EmailActiveSheet_WithMessage()
'the recipients are in an array
Dim ThisDate$, Recipient(1 To 10), N&
Dim SendersBook As Workbook
Dim RecipientsBook As Workbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Set a Codename for the senders book
Set SendersBook = Workbooks.Item(ActiveWorkbook.Name)
'Get the list of recipients - (in A1:A10 for this example)
For N = 1 To 10
With Worksheets(1)
Recipient(N) = .Range("A" & N)
End With
Next N
'Now create a new workbook to send
Workbooks.Add
ThisDate = Format(Date, "dd mmm yy")
ActiveWorkbook.SaveAs "File for " & ThisDate & ".xls"
'Set the Codename for this new workbook as "RecipientsBook"
Set RecipientsBook = ActiveWorkbook
SendersBook.Activate
'Copy the active sheet and paste it into the recipients book.
SendersBook.ActiveSheet.Copy Before:=RecipientsBook.Worksheets(1)
RecipientsBook.Activate
'Delete blank sheets in the recipients book
For N = 1 To 3
Sheets("Sheet" & N).Delete
Next N
'Send the new workbook with a message
ActiveWorkbook.HasRoutingSlip = True
With ActiveWorkbook.RoutingSlip
.Recipients = Recipient()
.Subject = "Files for " & ThisDate
'put your own message below
.Message = "Hi," & vbLf & _
"" & vbLf & _
"Attached files are for..." & vbLf & _
"...more here.... " & vbLf & _
"" & vbLf & _
"Regards," & vbLf & _
"Senders name" & vbLf & _
"" & vbLf & _
"" & vbLf & _
"" & vbLf & _
""
.Delivery = xlAllAtOnce
.ReturnWhenDone = False
End With
ActiveWorkbook.Route
'Delete the senders copy of the recipients book
'(this was only a temp book for the sender)
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
'Let user know what's happened
MsgBox "File sent by email ", , "Emailed..."
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Worksheets(1).Activate
End Sub
(The .ReturnWhenDone = False partly overcomes the problem that MOS spoke of)

HTH,
John

MOS MASTER
04-29-2005, 02:37 PM
Hi, :D

Don't have a clue right now to be honnest!

You're mailclient is one that I now nothing about.

I'll do a search for yah and see what I can come up with but it seams like looking for a needle a hay-stack..(If everything turnsout you need Outlook for this extra feature)

Can you confirm when you add the recipient youreself you are able to send email via this method?

I will be gone soon for tonight and hope on posting something good for you tommorow! :thumb

joelle
04-29-2005, 02:46 PM
Wow, John, I'm impressed by the codes.
I did try it but I got a run-time error that asked me to quit excel.. blah blah.
and the line below is hilighted.

ActiveWorkbook.SaveAs "File for " & ThisDate & ".xls"

I appreciate the effort though.


Hello Joost - have a good evening. Later.

Best,
Nee

sjvenz
04-30-2005, 04:13 AM
I don't know if these will work but give them a go




' Mail to a Group
Sub Mail_ActiveSheet()
Dim MailRecip As Variant
Dim wb As Workbook
Dim strdate As String
MailRecip = Array("uziel@xyz.com", "uziel@homeisp.com", "uziel@otherisp.com")
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
.SendMail Recipients:=MailRecip, Subject:="This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub


Sub Mail_ActiveSheet_Outlook()
'You must add a reference to the Microsoft outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "ron@debruin.nl"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Sub Mail_Every_Worksheet2()
Dim Sh As Worksheet
Dim wb As Workbook
Dim strdate As String
Dim MyArrIndex As Long
Dim E_Mail_Count As Long
Dim cell As Range
Dim MyArr() As String
Application.ScreenUpdating = False
For Each Sh In ThisWorkbook.Worksheets
If Sh.Range("a1").Value Like "*@*" Then
strdate = Format(Now, "dd-mm-yy h-mm-ss")

E_Mail_Count = Sh.Columns("A").Cells.SpecialCells(xlCellTypeConstants).Count
ReDim MyArr(1 To E_Mail_Count)
MyArrIndex = 1
For Each cell In Sh.Columns("A").Cells.SpecialCells(xlCellTypeConstants)
If cell Like "*@*" Then
MyArr(MyArrIndex) = cell.Value
MyArrIndex = MyArrIndex + 1
End If
Next
ReDim Preserve MyArr(1 To MyArrIndex)

Sh.Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Sheet " & Sh.Name & " of " _
& ThisWorkbook.Name & " " & strdate & ".xls"
.SendMail MyArr, _
"This is the Subject line"
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
End If
Next Sh
Application.ScreenUpdating = True
End Sub

MOS MASTER
04-30-2005, 11:59 AM
Wow, John, I'm impressed by the codes.
I did try it but I got a run-time error that asked me to quit excel.. blah blah.
and the line below is hilighted.

ActiveWorkbook.SaveAs "File for " & ThisDate & ".xls"

I appreciate the effort though.


Hello Joost - have a good evening. Later.

Best,
Nee
Hi Nee, :D

Like I promissed I did a little research for you.

The method that John provided uses the same object as I did so will fale on you're system also. (We must asume that you propably would need Outlook for this) A lot of the extra capabillitys of Office do only work if you have installed the full Office package.

If it was only a simple email you wanted to send then Excel can do that with no problem what so ever via the: "Send Email" method.

The problem is that you want to attach a workbook and give in recipients. It is a pitty the recipient thing doesn't work for you and it's quit amazing that the dialog method does in fact attach the workbook for you!

I looked for method's to utillize the netscape mailclient via VBA but I couldn't find an Netscape Object model anyware to work with.

I did found a quote of Excel MVP "John Peltier":

Here's what OLH says about the SendMail Method:

"Sends the workbook by using the installed mail system."

It doesn't matter what your email system is as long as it's known by the
system. That is, if you click on a mailto: link in your browser, this is the
email program that starts up.

If you want to do more than just send mail, you need somekind of object model
for your email system. But I've never heard of Netscape's object model being
exposed to MS VBA.

The last para is the problem here.

Automation via Netscape just isn't that easy. (Wouldn't say impossible..cause there is always someone who knows more)

That was my 2 cents worth for you! :friends: (Sorry but couldn't find any sollution for yah)

joelle
04-30-2005, 12:15 PM
Hello SJvenz, thanks a lot for the details code, but they still lose me (probably, as you saw MOSMASTER's last post, most codes only work for Outlook mail system).
Nee

Hello Joost!
How's your Saturday so far? Hope its enjoyable! (and not like mine)

I was typing a long back to sjvenz and when I hit "tab" I lost the whole msg. (headached "stacked").

Anyway, thank you for coming back to this post of mine.
And you told me what what I was afraid of -- that that if I stick to Netscape, looking for a solution would not be that straightforward.
So, I can transfer to Outlook no problem, but I have to make my codes work for any coworkers who are still using Netscape like I still do.
But thats my problem.
Thanks again for your patience and your effort to help. Of course, it is also so very nice of you to spend time on the research.
Enjoy your weekend,

Best regards,
Nee

MOS MASTER
04-30-2005, 01:04 PM
Hi Nee, :D

You're welcome!

Yes my Saturday was fine. It's a national holliday over here (Queensday) so I've had my share of drinks in the sun today!

So sorry you've lost you're message in the editor. (happens to me to from time to time)

It's always hard to automate something that will work for a big group of people who all have different configurations.

I hope someone will come up with an answer for you. (The question is still young)

Enjoy you're weekend! :whistle:

mdmackillop
12-12-2005, 05:24 PM
Hi Nee,
Did you try this KB item? http://www.vbaexpress.com/kb/getarticle.php?kb_id=310
Change the calling sub for Excel to

Sub eMailActiveWB()
Dim WB As Workbook

Application.ScreenUpdating = False
Set WB = ActiveWorkbook
WB.Save

SendIt "me@here.com", "A new Document", "Hi, read this:", WB.FullName

Application.ScreenUpdating = True
Set WB = Nothing
End Sub

joelle
12-12-2005, 06:45 PM
Hello Mdmackillop!

Wow, this thread is 8-month old, and I'm pleasingly surprised to see your post today.
Simply amazing! It has been one of my long-lasting puzzle too.
I just told Zack that I had to go home and cook (have stared at excel a whole day now [and everyday]), I still wanna stare at it but I cannot leave too much cooking to my Mom ... I cant wait to work with your codes though!!

Thanks soooo much,

Gd nite,
Nee