Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    bltomlinson
    Guest

    Oulook from Access

    Hi,
    Anybody an answer to, hopefully, a simple question. I have a database containing both e-mail addresses and names. Depending on the results of a query I want to populate the "To" field in Outlook with the relevant Names each being associated with the relevant e-mail address (the important issue is to show names). These are not contained in any address book only the access database. The user will complete the remainder of the e-mail and send it. I can get the names into the Outlook message with no problem the issue is the associated address, it seems that I ought to use the "Address" property but I can't seem to get the correct syntax and after exhaustive searches cannot find anywhere that describes it in detail. I am using Office97 - Any advise humbly accepted.
    Thanks
    Brian

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Oulook from Access

    Hi Brian,
    As far as I know, the only way you can do this is to create a recipient for each Name and Address - otherwise you will have to display the address rather than the name. With a recipient you can use the Name property for the display name and the Address property for the actual address.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    bltomlinson
    Guest

    Re: Oulook from Access

    Rory,
    Thanks for the prompt reply - I think this is where I'm going brain dead as I'm obviously missing something fundemental. The following snippet is where I'm trying to do exactly what you suggest by (looping through a recordset in practice) but I keep getting a syntax error.

    Set objRecipient = objMsg.Recipients.Add("Bert")
    objRecipients.Item(1).Address = "bert@somewhere.co.uk"

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Oulook from Access

    Brian,
    Sorry, I'm half asleep today! I should have said you need to create a new contact for each name and address. You could use code something like:
    Dim objMailItem As Outlook.MailItem, objContact As Outlook.ContactItem
    Dim objOutlook As New Outlook.Application
    With objOutlook
    Set objContact = .CreateItem(olContactItem)
    With objContact
    .FullName = "Bert"
    .Email1Address = "Bert@somewhere.com"
    .Save
    End With 'objContact
    Set objMailItem = .CreateItem(olMailItem)
    End With 'objOutlook
    With objMailItem
    .Recipients.Add objContact
    .Display
    End With 'objmailitem
    Set objContact = Nothing
    Set objMailItem = Nothing
    Set objOutlook = Nothing
    The drawback is you end up with an entry in your Contacts folder for each person, which you may not want. I guess you could loop through afterwards and delete them if necessary.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    bltomlinson
    Guest

    Re: Oulook from Access

    Rory,
    Sorry this is beginning to become a saga. Thanks for your advice, I've tried it and a couple of variations, the code certainly adds a person to the Contacts folder (not too bothered about that) but it only returns the name as a recipient and not the associated e-mail address.
    Thanks
    Brian

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Oulook from Access

    Hi Brian,
    I was under the impression from your first post that you wanted to display the names and not the addresses (the addresses are of course associated with the names as far as Outlook is concerned.) Is that not the case? If not, can you clarify why you need the address as well as the name and perhaps I or someone else can come up with a solution?
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    bltomlinson
    Guest

    Re: Oulook from Access

    Rory,
    Thanks again for your reply. You are absolutely correct I do want to show names in the "To" field and have relevant addresses associated with them e.g. right click on name and properties should show the e-mail address. Unfortunately using the contacts method the name is shown but no email address is associated with it.
    Just a bit of further background I managed to achieve exactly what I wanted using MAPI and the following code -

    Dim objSession As MAPI.Session
    Dim objMessage As MAPI.Message
    Dim objRecipient As MAPI.Recipient

    Set objSession = CreateObject("mapi.session")

    ' Specify valid profile name to avoid dialog box
    objSession.Logon profilename:="MS Exchange Settings"

    ' add a new message to Outbox
    Set objMessage = objSession.Outbox.Messages.Add

    ' set properties of recipient object
    Set objRecipient = objMessage.Recipients.Add

    With objRecipient
    .Name = "bert"
    .Address = "smtp:bert@somewhere.co.uk"
    .Type = mapiTo
    End With


    ' send the message
    objMessage.Send showdialog:=True

    unfortunately, there has to be one of course, the users found that they could not minimise the message box neither could the copy from another application and paste into the message body - hence trying to pursue the Outlook option.
    Sorry about the length of this diatribe but I hope that puts the problem into perspective - If you have any solutions to the MAPI problem that of course would be the answer to a maidens prayer <g>
    Thanks again
    Brian

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Oulook from Access

    <hr>Unfortunately using the contacts method the name is shown but no email address is associated with it.<hr>
    Now I am confused! I tested the code and it did exactly what I expected it to - created a contact called Bert with email address bert@somewhere.com, which I could view in my Contacts folder. Did it not do that for you? Bear in mind that until Outlook resolves the recipient names, you won't be able to view their properties by right-clicking on the name in the To field. It tends to resolve them once you've tabbed off the To field but you can force it in code by adding an objMail.Recipients.ResolveAll line to your code. Does that help?
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    bltomlinson
    Guest

    Re: Oulook from Access

    Rory,
    Your confused, that's two of us <g>. I'm attaching all the information I can supply. I've taken the code and put in in VB6 with a reference to Outlook to make life simpler (I hope). It certainly creates a Contact called "Bert" with an e-mail address of "bert@somewhere.co.uk" but well I think the attachment is self explanatory.
    Thanks
    Brian

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Oulook from Access

    Brian,
    Can you try that again as it didn't attach? Also I'm not sure how transferring your code from Access to vb6 is going to make life simpler (it certainly won't for me!) Can you attach it as a scaled down version of your database? (or email it to me if it's too big and I'll post any ideas back here)
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Oulook from Access

    Brian,
    It just occurred to me that there are certain types of file attachments that aren't allowed here (for safety) and .ex? and .vb? files are among those not allowed. Can you zip a small version of your db here or to me?
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    bltomlinson
    Guest

    Re: Oulook from Access

    Rory,
    I've tried to send this once "Outlook.zip" but was knocked off so hope it's not duplicated. Enclosing very cut down version of database one table "recipients" one form "frmEmail" containing command button which should generate an e-mail containing all names and associated email addresses. If you tell me it works I'll join the Legion, not such a bad idea anyway.
    Regards
    Brian

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Oulook from Access

    Hi Brian,
    It's still not attached! Are you previewing your message after you attach it? That will lose the attachment so you need to simply Post It once you've entered the location of your file.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    bltomlinson
    Guest

    Re: Oulook from Access

    Rory,
    I just did a spell check, attached the file and posted it. What I have done is to forward the attachment and the word document to your AOL address.
    Brian

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Oulook from Access

    Hi Brian,
    I got your file (the reason it wouldn't attach is because it was 1.3MB and the attachment limit is 100KB!) and I was about to ask you how good your French is since it worked perfectly. Then I thought "What if I log out of Outlook and then run it?" and sure enough I got the same thing you did! I would suggest adding a line like:
    objOutlook.getnamespace("Mapi").Logon
    to your code which should then solve your problem. This does seem to slow down your code a little so I'll work on something a bit quicker if I get a minute, unless anyone else cares to chip in and save me the effort? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

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
  •