• Re: VBA email cannot work

    From Peter T@21:1/5 to All on Mon Dec 27 16:43:45 2021
    XPost: microsoft.public.excel, microsoft.public.excel.programming

    #If VBA Then ...#Else...#End If
    Typo - should read #If VBA7

    PT

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Robert Baer@21:1/5 to Peter T on Mon Dec 27 22:31:17 2021
    XPost: microsoft.public.excel, microsoft.public.excel.programming

    Peter T wrote:

    #If VBA Then ...#Else...#End If
     Typo - should read #If VBA7

    PT
    The compiler hates comments.


    --
    This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Philip Herlihy@21:1/5 to All on Tue Jan 4 10:44:25 2022
    XPost: microsoft.public.excel, microsoft.public.excel.programming

    In article <16c6f4b4a8ff960a$1$43267$e2dde862@news.thecubenet.com>, robertbaer@localnet.com says...

    Peter T wrote:
    On 28/12/2021 06:18, Robert Baer wrote:
    Peter T wrote:
    On 27/12/2021 07:26, Robert Baer wrote:


       Problem: the "Private Declare Function ShellExecute Lib
    "shell32.dll" Alias "ShellExecuteA" () produces the error message
    "Only comments may appear after End Sub, End Function, or End Property" >>>>
       So I moved the #If..#End If to after the End Sub; no joy.

    No don't do that, the APIs must be declared at module level.
    * Have no way to access module level, unless there a hidden trick.

    All API's should be declared (ie written) at the TOP of a module, in
    this case a 'normal' module.

    The term "module level" means the TOP of a module where any any code
    goes that is not a procedure eg like your Sub SendEMail(). This is where external functions (APIs) are 'declared', also variables which you want
    to retain 'scope' and possibly share with multiple procedures.

    As I mentioned your code really does work. If still struggling upload a workbook with it to a file sharing site and post the link.

    Peter T




    I have another option to send emails: send personalized mass emails
    to a list from Excel via Outlook.
    So I follow the script:
    1. Launch a new blank Word document, and then click Mailings > Select Recipients > Use an Existing List.
    2. In the Select Data Source window, choose the workbook which includes
    the data range that you want to use, and click Open button.

    Well that is impossible, because there is NO Select Data Source anywhere.

    So it seems that there is no way in heck i can send e-mails from a
    list, and will be forced to do it one-by-one manually, 2500 times.

    Thanks, R. Baer

    If you have Outlook application available then that's the way to go. I saw your original (and creative!) solution was to build a 'mailto:' link and then try to 'click' it within the VBA - I wouldn't have time to experiment with that but I was surprised! If you have Outlook installed, you can simply create an instance of it within your Excel VBA, and that gives you all the options you could want. This link (which I haven't studied in depth, so look for others if you hit snags) seems to run through the process, and you can adapt what it sends to suit your needs. https://www.automateexcel.com/vba/send-emails-outlook/

    --

    Phil, London

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Robert Baer@21:1/5 to Peter T on Sun Jan 9 02:40:02 2022
    XPost: microsoft.public.excel, microsoft.public.excel.programming

    Peter T wrote:
    On 04/01/2022 01:08, Robert Baer wrote:
    Peter T wrote:
    On 28/12/2021 06:18, Robert Baer wrote:
    Peter T wrote:
    On 27/12/2021 07:26, Robert Baer wrote:


    Problem: the "Private Declare Function ShellExecute Lib
    "shell32.dll" Alias "ShellExecuteA" () produces the error message
    "Only comments may appear after End Sub, End Function, or End
    Property"

    So I moved the #If..#End If to after the End Sub; no joy.

    No don't do that, the APIs must be declared at module level.
    * Have no way to access module level, unless there a hidden trick.

    All API's should be declared (ie written) at the TOP of a module, in
    this case a 'normal' module.

    The term "module level" means the TOP of a module where any any code
    goes that is not a procedure eg like your Sub SendEMail(). This is
    where external functions (APIs) are 'declared', also variables which
    you want to retain 'scope' and possibly share with multiple procedures.

    As I mentioned your code really does work. If still struggling upload
    a workbook with it to a file sharing site and post the link.

    Peter T




    Code as given will be found at: http://www.oil4lessllc.org/Excel/

    You uploaded the code as a text file, same as you posted here albeit
    without the line wrapping.

    As before the code works but we need to see how it's implemented in your workbook. If you can, add it to workbook as you already have it, with
    some sample email data on the worksheet, and upload it.

    Nobody told me where and how to place code, and there are 5 places:
    Sheet Modules under Microsoft Excel Objects (the default location),
    User form under Forms, Modules under Modules, Class under Class
    modules, ThisWorkbook under Microsoft Excel Objects and I hear Code
    Modules but under what I do not know.

    As Adrian tried to explain it is incumbent on you to learn all this, if nobody told you there are plenty of resources. Though it has been
    explained in this thread the code needs to go in a 'normal' module, the
    type of module that gets added automatically when you record a macro.

    Also, nobody said the code had different parts: the
    procedure/function part which was given inline with the rest of the
    given code; then the unstated option part.
    Naturally, where each part must be.

    Nobody said...

    And you added another variable TOP; I assume that means the first
    module if there is more than one in any of the 5 sections I have listed.

    Top of the module (any/all modules have a Top) is where 'module level declarations' go, such APIs and variables which have 'scope' throughout
    the module( possibly plural but another subject). Procedures, like
    Sub's and Functions, go below.

    Peter T
    Had some other computer problems had to fix, but got the XLS
    file/program uploaded to http://www.oil4lessllc.org/Excel/.

    I do not understand what you mean by Top.

    Thanks.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)