Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Macro that sets up an email?

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Does anyone know how I can do a macro that will set up a mail to be sent. I've seen it done, where you click on a button and a mail comes up with a set address written in and a subject filled in.

    Can anyone tell me how to do it? By the way, I'm using Outlook 98

    Thanks in advance

  2. #2
    New Member
    Join Date
    Feb 2002
    Location
    London / NZ
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,
    See the code below - this is a example of how to automate the sending of emails with attachments. If you step through the code you will see which lines to change to suit your needs - you can attach multiple files by repeating the "attachments.add" instruction.

    Sub OUTLOOK_test_Email()

    'creates link to Outlook
    Dim objOL As New Outlook.Application
    Dim objMail As MailItem

    On Error Resume Next

    Set objOL = New Outlook.Application
    Set testsemail = objOL.CreateItem(olMailItem)



    'sets charactersitics for email
    With testsemail
    'requests a read confimeration receipt for the email
    .ReadReceiptRequested = True
    'sets receipent
    .To = "Testing, Testing"
    'enters subject
    .Subject = "Test Email"
    'displays email to allow manipulation fro comments
    .Display
    'enters text for email
    .Body = "Testing Email"
    attach1 = .Attachments.Add("c:temptest.xls", , 60)
    End With

    End Sub


    Hope this helps,

    Dognz

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks that's brilliant! I'll try it out and get back to you

    Thanks again


  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi, sorry about this but it doesnt seem to work. I copied the code exactly and when I use it I get:

    Compile Error: User defined type-not defined

    Any suggestions?

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    UK - London
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you just want to send the active open workbook, you could use Excel's "Send to Mail Recipient as Attachment" function (located in the file menu.
    In VBA this would be:

    Application.Dialogs(xlDialogSendMail).Show _
    arg1:="e-mail address", _
    arg2:="Subject Line"

    arg1 is the e-mail address in standard format (is x@xy.com etc)
    arg2 is what will appear in the subject line.

    The result of this code is to open a new e-mail window, attach the file and complete the To: and Subject lines - you have to manually press SEND.

    Hope this helps
    D

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That's great, but all I want to do is when the button is pressed is for a blank email to come up, no file attached, but the address filled in, and the subject filled in.

    I dont mind manually clicking send, but I don't want the file attached

  7. #7
    New Member
    Join Date
    Feb 2002
    Location
    London / NZ
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    what line of the code do you get the compile error?

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On these lines:

    Sub OUTLOOK_test_Email()

    Dim objOL As New Outlook.Application


  9. #9
    New Member
    Join Date
    Feb 2002
    Location
    London / NZ
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You neeed to make sure you have references to the Microsoft Outlook Object Library activated.

    To do this go to Tools|Referecnes in the Visual Basic Editor and select the Check box next to Microsoft Outlook Object Library.

    This will then allow your Excel VBA to understand the Outlook commands that were causing you the compile errors - hopefully!

    If you do not want to attach a workbook to the email - just delete the relavent line of code.



  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Superb! All working now!

    Thanks a lot guys

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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