Consulting

Results 1 to 20 of 20

Thread: Solved: Pls HELP!! w/ email a worksheet

  1. #1
    VBAX Tutor joelle's Avatar
    Joined
    Apr 2005
    Location
    Sunnyvale, CA
    Posts
    204
    Location

    Solved: Pls HELP!! w/ email a worksheet

    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
    ======================

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Hello Nee, and welcome to the board!!

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

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

    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.

  3. #3
    VBAX Tutor joelle's Avatar
    Joined
    Apr 2005
    Location
    Sunnyvale, CA
    Posts
    204
    Location
    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:
    ==========================
    [vba]Sub SendIt()
    '
    ' SendIt Macro
    ' Macro recorded 4/28/2005 by Nee
    '

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

    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

  4. #4
    I have used this. I got this one from here and works great.


    [VBA]
    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

    [/VBA]

  5. #5
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Hi and welcome to VBAX!

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

    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!
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  6. #6
    VBAX Tutor joelle's Avatar
    Joined
    Apr 2005
    Location
    Sunnyvale, CA
    Posts
    204
    Location
    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

  7. #7
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Seams you don't like jokes...no problem!

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

    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?
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  8. #8
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  9. #9
    VBAX Tutor joelle's Avatar
    Joined
    Apr 2005
    Location
    Sunnyvale, CA
    Posts
    204
    Location
    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

    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
    ============================

  10. #10
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Hi Nghi,

    Nice to meet yah...and please..I don't get offended that easily! (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:[VBA]
    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
    [/VBA]
    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!
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  11. #11
    VBAX Tutor joelle's Avatar
    Joined
    Apr 2005
    Location
    Sunnyvale, CA
    Posts
    204
    Location
    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

  12. #12
    Administrator
    Chat VP
    VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    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)[vba]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[/vba]
    (The .ReturnWhenDone = False partly overcomes the problem that MOS spoke of)

    HTH,
    John
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  13. #13
    Administrator
    VP-Knowledge Base VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Hi,

    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!
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  14. #14
    VBAX Tutor joelle's Avatar
    Joined
    Apr 2005
    Location
    Sunnyvale, CA
    Posts
    204
    Location
    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

  15. #15
    VBAX Regular
    Joined
    Mar 2005
    Posts
    15
    Location
    I don't know if these will work but give them a go




    [vba] ' 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[/vba]

  16. #16
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Quote Originally Posted by Nee
    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,

    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! (Sorry but couldn't find any sollution for yah)
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  17. #17
    VBAX Tutor joelle's Avatar
    Joined
    Apr 2005
    Location
    Sunnyvale, CA
    Posts
    204
    Location
    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

  18. #18
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Hi Nee,

    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!
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  19. #19
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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
    [VBA]
    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

    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  20. #20
    VBAX Tutor joelle's Avatar
    Joined
    Apr 2005
    Location
    Sunnyvale, CA
    Posts
    204
    Location
    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

Posting Permissions

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