Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    New Lounger
    Join Date
    Feb 2002
    Location
    Orangeville, Ontario, Canada
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SendMail Problem (English/NT4.0/SP6)

    First let me say that I am a beginner when it comes to macros....

    I am having problems sending .zip files using Visual Basic in Excel. With the code below Excel opens the zip file which is of course not readable and sends the file. The problem is when I attempt to open the .zip file WINZIP does not recognize the file format.

    Is there a way to attach a file instead of opening it? I think this would solve my problem. Or perhaps there is a way to use VB without going through Excel. I hope I have explained this well enough.

    Thank you for your help.

    Dave.


    Sub mail_brand_evaluation_zipfiles()


    ChDrive "M:"

    ChDir "M:Shared FilesInformation ServicesFTPfilesBrand Evaluation"

    Workbooks.Open Application.GetOpenFilename("Brand Evaluation Files (*.zip), *.zip")

    ActiveWorkbook.SendMail Recipients:=Array("dave.ramsay@mymail.ca"), _
    Subject:="Here is your report"

    ActiveWorkbook.Close

    End Sub

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SendMail Problem (English/NT4.0/SP6)

    Look up SendMailAttach in the object browser.

  3. #3
    New Lounger
    Join Date
    Feb 2002
    Location
    Orangeville, Ontario, Canada
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SendMail Problem (English/NT4.0/SP6)

    Hi Howard,

    Thank you for your reply. I went to look up SendMailAttach and the only items that I found were SendMail and SendMailer.
    Can you tell me where to go? Please be nice [img]/forums/images/smilies/ohmy.gif[/img])

    Thanks,

    Dave.

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SendMail Problem (English/NT4.0/SP6)

    Ah, you are using Excel, I was looking in the Word Object Browser.

    I would think you could do the deed with the xlDialogSendMail.

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: SendMail Problem (English/NT4.0/SP6)

    If you also use Outlook, you can automate Outlook to create a new message, insert a body and recipient, attach a file, and send it off (or leave it unsent on the screen for review and editing).

    For a couple of points of reference on how to do this, take a look at the following posts:

    <!post=Post #159863,159863>Post #159863<!/post> - Basic example of creating and sending a new message from Word VBA

    <!post=Post #61250,61250>Post #61250<!/post> - Basic example of creating a message and attaching the currently open document in Word VBA

    <!post=Post #133130,133130>Post #133130<!/post> - Basic example of creating a message and attaching a saved CSV file in Access VBA

    <!post=Post #162170,162170>Post #162170<!/post> - More complicated example extracting info from the open document in Word VBA

    <!post=Post #169903,169903>Post #169903<!/post> - Even more complicated example addressing and creating message from inside Access VBA

    When you choose Debug|Compile..., if you get complaints about user-defined objects, go into Tools|References... and add a reference to the Microsoft Outlook object library for your version of Outlook.

    Hope this helps.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: SendMail Problem (English/NT4.0/SP6)

    Here is some code that should work in Excel, and apart from GetOpenFilename should work in Word and other flavours of VBA also.<pre>Sub SendFile()
    Dim objOLook
    Dim objOMail
    Set objOLook = CreateObject("Outlook.Application")
    Set objOMail = objOLook.CreateItem(olMailItem)
    ChDrive "M:"
    ChDir "M:Shared FilesInformation ServicesFTPfilesBrand Evaluation"
    With objOMail
    .To = "Recipients"
    .Subject = "Report"
    .Body = "Here is your report"
    .Attachments.Add Application.GetOpenFilename _
    ("Brand Evaluation Files (*.zip), *.zip")
    .Send
    End With
    Set objOMail = Nothing
    Set objOLook = Nothing
    End Sub</pre>

    You may want to incorporate error checking to handle cancellation of the file open dialog. Also change Recipients to valid email address(es)

    Andrew C

  7. #7
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: SendMail Problem (English/NT4.0/SP6)

    > Or perhaps there is a way to use VB without going through Excel.

    Yes, these same things can be implemented using VBScript, and you can keep a little .VBS file on your desktop for that purpose. I haven't added any user interface to this, but if you create a new text document, paste this in, fix the e-mail address and file name, save, and then run it. it should immediately send the message. If Outlook is not already open, it won't be quite so immediate.

    'This script will mail a specific document
    Dim olApp, olMsg
    Set olApp = WScript.CreateObject("Outlook.Application")
    Set olMsg = olApp.CreateItem(0) '0 = olMailItem
    olMsg.Recipients.Add("dave.ramsay@mymail.ca")
    olMsg.Subject = "Here is your report"
    olMsg.Attachments.Add "M:Shared FilesInformation ServicesFTPfilesBrand Evaluationfilename.zip"
    olMsg.Body = "Please call me with any questions concerning this report." & vbCrLf & vbCrLf & "-Dave"
    ' To allow for editing, could change next line to olMsg.Display
    olMsg.Send
    ' Could do: MsgBox "Message handed off to Outlook for sending!"
    Set olNewMsg = Nothing
    Set olApp = Nothing

    There is a downside to setting the .Body property this way. If you're using Outlook 2000 and your default format is plain text, assigning a string to the .Body will change it to RTF format. Some mail readers cannot handle RTF and will either not show the attachment will show a generic winmail.dat attachment. There's just no perfect solution to any problem is there?!

  8. #8
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SendMail Problem (English/NT4.0/SP6)

    He did not seem to be asking for an Outlook solution.
    Using Outlook via Automation is better, IMHO, than using Send, if one wishes to use Outlook.

  9. #9
    New Lounger
    Join Date
    Feb 2002
    Location
    Orangeville, Ontario, Canada
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SendMail Problem (English/NT4.0/SP6)

    Thank you for your replies Howard, I appreciate them very much.
    I have tried a couple of solutions that other readers have suggested and they seem to do what I need.

    Thanks again,

    Dave.

  10. #10
    New Lounger
    Join Date
    Feb 2002
    Location
    Orangeville, Ontario, Canada
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SendMail Problem (English/NT4.0/SP6)

    Hi Andrew,

    Thank you so much for your solution it works very well for my needs. As I explained in my original message "I am new at this" so I would like to ask one more question if I may. Is there a way that I can add my default Outlook signature to these messages?

    Thanks again,

    Dave.

  11. #11
    New Lounger
    Join Date
    Feb 2002
    Location
    Orangeville, Ontario, Canada
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SendMail Problem (English/NT4.0/SP6)

    Hi Jefferson,

    Thank you for your suggestions. I also like this one which works well for me. There are a couple of questions if I may, how can I change the code to allow me to choose the files I wish to send? Also is there a way for me to add my default Outlook signature to the emails?

    Thanks again,

    Dave.

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: SendMail Problem (English/NT4.0/SP6)

    ESP ? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  13. #13
    New Lounger
    Join Date
    Feb 2002
    Location
    Orangeville, Ontario, Canada
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SendMail Problem (English/NT4.0/SP6)

    I guess I should have included your reply on the last email, sorry. It is below.

    My question is, is there a line of code that can be used which will add my default Outlook signature to the email?

    Thanks again,

    Dave.


    Here it is.....

    Here is some code that should work in Excel, and apart from GetOpenFilename should work in Word and other flavours of VBA also.

    Sub SendFile()

    Dim objOLook
    Dim objOMail
    Set objOLook = CreateObject("Outlook.Application")
    Set objOMail = objOLook.CreateItem(olMailItem)

    ChDrive "M:" ChDir "M:Shared FilesInformation ServicesFTPfilesBrand Evaluation"
    With objOMail
    .To = "Recipients"
    .Subject = "Report"
    .Body = "Here is your report"
    .Attachments.Add Application.GetOpenFilename _
    ("Brand Evaluation Files (*.zip), *.zip")
    .Send

    End With

    Set objOMail = Nothing

    Set objOLook = NothingEnd Sub

    You may want to incorporate error checking to handle cancellation of the file open dialog. Also change Recipients to valid email address(es)

    Andrew C

  14. #14
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: SendMail Problem (English/NT4.0/SP6)

    <P ID="edit" class=small>(Edited by jscher2000 on 13-Sep-02 14:23. I was unclear. Sorry.)</P>> how can I change the code to allow me to choose the files I wish to send?
    If you want to do it from inside Excel, I think others know better than I. If you mean in the VBScript file, I don't think I can add any kind of dialog interface to that. It's not intended to present much of a user interface. What you could do is have the script open Windows Explorer to the proper folder so you could drag and drop the files into the open message. I don't know if that method appeals to you.

    > Also is there a way for me to add my default Outlook signature to the emails?
    You know, I don't use an automatic signature, so I don't even know whether creating a message using automation from outside Outlook adds the signature automatically or not. I gather from your question that it doesn't.

    In the absence of insight from another Outlook user, your best bet is to find the signature file, which likely is a .txt file, and append the contents of the file to the .Body of the message. I don't have time right now (starving! must eat!) to write the code for that, but there are several ways to read the text out of a .txt file into a string variable, which you then can tack on to the message body.

    Hope this helps.

  15. #15
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: SendMail Problem (English/NT4.0/SP6)

    Dave,

    I do not know if there is a way to include your signature via code - I have not been able to find it, sorry. If it is just text, perhaps you can copy it into the thre code as a string and use it in the body text.

    Regarding a method of selecting a file whilst working with the VBScript solution, try the following amendment to Jefferson's code :
    Dim olApp, olMsg
    Dim strFile, strTitle, strStartPath
    strTitle = "Select file for attachment :"
    strStartPath= "M:Shared FilesInformation ServicesFTPfilesBrand Evaluation"
    strFile = SelectFile(strTitle, strStartPath)

    If len(strFile) > 0 then
    Set olApp = WScript.CreateObject("Outlook.Application")
    Set olMsg = olApp.CreateItem(0) '0 = olMailItem
    olMsg.Recipients.Add("dave.ramsay@mymail.ca")
    olMsg.Subject = "Here is your report"
    olMsg.Attachments.Add strFile
    olMsg.Body = "Please call me with any questions concerning this report." & _
    vbCrLf & vbCrLf & "-Dave"
    ' To allow for editing, could change next line to olMsg.Display
    olMsg.Send
    ' Could do: MsgBox "Message handed off to Outlook for sending!"
    End If

    Set olNewMsg = Nothing
    Set olApp = Nothing

    Function SelectFile(strTitle, strStartPath)
    Dim wshShell, w######em
    Set wshShell = WScript.CreateObject("Shell.Application")
    On Error Resume Next ' in case user selects Cancel
    Set w######em= wshShell.BrowseForFolder(&H0, strTitle, &H4000, strStartPath)
    SelectFile = w######em.ParentFolder.ParseName(w######em.Title). Path
    On Error Goto 0
    Set wshShell = Nothing
    Set w######em = Nothing
    End Function

    This worked ok for me in WindowsME, but was unpredictable in Windows XP. I did not get the chance to try it on a NT system. It uses the Shell's BrowseForFolder object, but by setting the flags to &H4000, you can include and select files (single items only). Give it a try on NT and let us know.

    Andrew C

Page 1 of 2 12 LastLast

Posting Permissions

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