Sending email from VBA (Excel) with Outlook

SaskiaHe

New Member
Joined
Apr 14, 2009
Messages
20

<button title="This question does not show any research effort; it is unclear or not useful" class="js-vote-down-btn grid--cell s-btn s-btn__unset c-pointer" aria-pressed="false" style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; border-bottom-color: currentColor; border-bottom-left-radius: 3px; border-bottom-right-radius: 3px; border-bottom-style: none; border-bottom-width: medium; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: currentColor; border-left-style: none; border-left-width: medium; border-right-color: currentColor; border-right-style: none; border-right-width: medium; border-top-color: currentColor; border-top-left-radius: 3px; border-top-right-radius: 3px; border-top-style: none; border-top-width: medium; box-shadow: none; box-sizing: inherit; color: inherit; cursor: default; display: inline-block; font-family: inherit; font-size: inherit; font-size-adjust: none; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin-bottom: 2px; margin-left: 2px; margin-right: 2px; margin-top: 2px; outline-color: invert; outline-style: none; outline-width: medium; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; position: relative; text-align: center; text-decoration: none;" aria-label="down vote" data-selected-classes="fc-theme-primary"><svg xmlns="http://www.w3.org/2000/svg" class="svg-icon m0 iconArrowDownLg" aria-hidden="true" style="box-sizing: inherit; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; vertical-align: bottom;" viewBox="0 0 36 36" width="36" height="36">
</svg></button> <button title="Click to mark as favorite question (click again to undo)" class="js-favorite-btn s-btn s-btn__unset c-pointer py8" aria-pressed="false" style="background-attachment: scroll; background-clip: border-box; background-color: transparent; background-image: none; background-origin: padding-box; background-position-x: 0%; background-position-y: 0%; background-repeat: repeat; background-size: auto; border-bottom-color: currentColor; border-bottom-left-radius: 3px; border-bottom-right-radius: 3px; border-bottom-style: none; border-bottom-width: medium; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: currentColor; border-left-style: none; border-left-width: medium; border-right-color: currentColor; border-right-style: none; border-right-width: medium; border-top-color: currentColor; border-top-left-radius: 3px; border-top-right-radius: 3px; border-top-style: none; border-top-width: medium; box-shadow: none; box-sizing: inherit; color: inherit; cursor: default; display: inline-block; font-family: inherit; font-size: inherit; font-size-adjust: none; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; outline-color: invert; outline-style: none; outline-width: medium; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; position: relative; text-align: center; text-decoration: none;" aria-label="favorite" data-selected-classes="fc-yellow-600"> <svg xmlns="http://www.w3.org/2000/svg" class="svg-icon iconStar" aria-hidden="true" style="box-sizing: inherit; vertical-align: bottom;" viewBox="0 0 18 18" width="18" height="18">
</svg>
</button>


I used to send email from VBA from an Excel Workbook. This always worked correctly.Now I have a new PC and tried to run the same code. And I get a permission error. I have checked, re-checked and double checked with my old PC, but I can't find any difference in settings. Not in Excel, nor in Outlook.

Code:
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    

    [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Set objOl = CreateObject("Outlook.Application")
    [/FONT]Set objMail = objOl.CreateItem(0)
    With objMail
        .Sentonbehalfofname = Sender
        .Subject = qrySubj
        .HTMLBody = "<HTML>******>" & Text & "</BODY></HTML>"
        .DeleteAfterSubmit = False
        .To = Receiver
        If Not IsMissing(When) Then .DeferredDeliveryTime = DateAdd("n", When, Now)
        .Send
    End With
[/FONT]

I tried objmail.from in stead of .
Sentonbehalfofname but then I get error 434 during execution. Property or method not being support by object.

<strike></strike>


<strike></strike><strike></strike>
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,768
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
Are you able to open outlook and send an email manually on behalf of the shared mailbox
Has your outlook profile been updated to include the shared mailbox
 

SaskiaHe

New Member
Joined
Apr 14, 2009
Messages
20
To be honest ... I'm not aware of any shared mailbox ...

On my new and old PC:
I'm using mail address xxx@bid72.com from which I can send mails with outlook.

On my old PC: when I'm trying to send mails from xxx@bid72.com with Excel/VBA and outlook, then it is using my hotmail-account to send the mails. But the bid72-address is still the sender.

On my new PC: I get the permission error when using Excel/VBA and Outlook.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,002
Messages
5,545,440
Members
410,684
Latest member
LakTik
Top