Results 1 to 10 of 10

Thread: Urgent support needed. Multiple emails, multiple Excel workbooks, at once.

  1. #1
    Junior Member
    Join Date
    Feb 2018
    Posts
    1
    Rep Power
    0

    Cool Urgent support needed. Multiple emails, multiple Excel workbooks, at once.

    Hi great people,

    my manager asked me to do a stupid task that takes a lot of time. since i am smart and i have no knowledge about coding, i need your help.

    The task is,
    send around 700 emails (same title and same text) but to different respondents.
    each email should contains an excel workbook.

    What i have:
    +Excel sheet that has: column A with the name of companies, column B with different emails addresses (for the same company) separated by ;
    + file that has all excel workbooks (each workbook has the same name as the company as in column A)


    If you can help me with some type of micro, that will be great.


    Thank you so much.

  2. #2
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    Last edited by DocAElstein; 06-11-2023 at 01:14 PM.

  3. #3
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Here’s my 'micro': ---- :)

    Hi reda2006
    Welcome to the excelfox Forum.

    I have a good basic VBA Knowledge and automate a lot of my daily stuff with it.
    But I have never sent an Email using VBA.
    But I think I can manage a micro response for you:

    Here’s my 'micro':
    Do a simple Internet search with terms like
    __ send email using vba
    or better still
    __ send email using vba youtube

    You will see that you are flooded with a lot of tutorials showing you how to do it.
    I just took a quick look and I think some of them are very easy to follow.
    I like this guy, he is very slow and thorough
    https://www.youtube.com/watch?v=pFl7W8d7d4M
    But best is to go through a few of the vidios.
    What I would ( will probably later**** ) do is download them all, make a play list and let it run in the background for a few days while I do something else ..

    They will tell you how to send a single email I expect. I think I noticed some showing how to add an attachment as well.
    It is very simple basic VBA stuff to automate something to be done a lot of times once you have figured out how to do it for the single case.
    It is also very simple VBA stuff to read the email addresses and the file names from the lists as you described them
    You will need to know a bit of basic VBA stuff, but you really should do that even if you are doing it as a one off.
    If you do anything at all with Excel then you will find that some things are incredibly easier with a bit of simple coding.
    Most Excel stuff itself is just a lot VBA coding already written. So VBA is crying out to be used to tailor things more exactly and efficiently to your requirements.

    So the best thing to do is to:
    _ get a bit clued up on how to do it.
    _ while you are doing that , make up a mock up file / files with desensitised dummy data. Keep the data to a minimum but pick it carefully to test out all scenarios.


    Try and get that working. Usually a code working on a small amount of data will work on a much larger amount with little or no modification
    _..____________

    So, there you go - That was a fairly micro response, at least very micro by my standards.

    ****Having said that I might be interested in trying to automate sending some files by E-Mail myself sometime in the next week or so. Let us know how you get on and might be able to help later.
    But if it is urgent, then as snb suggested, a Forum probably isn’t the best place to ask. A Forum is more of a “self help group” really. We all have a lot of other stuff to do, and rarely anyone responds to an “Urgent” appeal here.
    Anyway I will take a look at this in few days for my own case, so might post back , and will certainly take a look to see if you have posted anything
    Good luck

    Alan

    _._____

    EDIT: If you look at the bottom of this Thread you will often see suggestions for similar Threads:
    VBASendEMailThreadSuggestions.JPG : https://imgur.com/qt1x7Mf
    VBASendEMailThreadSuggestions.JPG
    VBASendEMailThreadSuggestions2.JPG : https://imgur.com/X5zLEox
    VBASendEMailThreadSuggestions2.jpg

    It might be worth taking a look at those as well




    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 06-11-2023 at 01:15 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  4. #4

  5. #5
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    I Don't think I have Outlook, .. currently installed :(

    Thanks snb.
    I don't seem to have Outlook. I mostly have Excel2007 and 2010 currently. All Student and Home editions

    ( But I have collected some Excel 2003 discs as I was thinking of going "retro" and using Excel 2003.
    Apparently it should be in there. It doesn't tell me on the disc what is in them and those discs that I have used already I custom installed just Excel. I don't remember if I saw Outlook listed. But apparently it should be there:
    http://www.itprotoday.com/office-365...tions-compared
    Maybe I will take another look if I install Outlook sometime )

    Alan
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  6. #6
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    If you could install Excel 2010 you were also able to install Outlook.

    For other methods see: http://www.snb-vba.eu/VBA_Excelgegevens_mailen_en.html

  7. #7
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Hmm it is but isn't with Office 2010, maybe an update thing is ( was )

    Quote Originally Posted by snb View Post
    If you ... install Excel 2010 .... also ....l Outlook.
    Correct . ( . Edit - actually not.. or maybe later sort of ) ! My mistake.
    EDit. If I can Install Excel 2010 , I cannot install Outlook , not currently with the Office 2010 instillation disks that I have. ( I have 3 of them, each has 3 valid licences, approximately half the licences are in use by me or my wife )


    This was my current situatuon and mistakenly I did not realise that I do indeed habe Outlook (and Access) in my Office2010 instillations.
    It is not in my Offfice2007_..
    OutlookNotInExcel2007.JPG : https://imgur.com/jSp2eEb
    OutlookNotInExcel2007.JPG
    -.. so I assumed it wasn’t in my Offfice2010 either.

    But I now see that it is currently in my Office2010
    snbWasRightOutlookIsInMyExcel2010.JPG : https://imgur.com/vuRcrlt
    snbWasRightOutlookIsInMyExcel2010.JPG

    Strange it is not in Office2007 .. or maybe not strange .. maybe there is an explanation**

    Thanks for pointing that out and adding the other link

    This Outlook business looks like a big subject.. maybe it will be a bit longer until I post back here with my attempt at Email with VBA


    _.___________

    Edit: I am confused.. On my Disk boxes it says I don't have it, ACCESS neither
    https://imgur.com/OblEx82 : Office 2010
    ( there are 3x3=9 Office 2010 licences there in those 3 disks
    I used those disks some time ago ( approx 2 years ago on avarage , possibly sooner in some cases ) . I do not think I had the option for Outlook or Access when I instilieret.

    ( I also did 3 instillations a long time ago from this 3 licence Office 2007 disk
    ( https://imgur.com/PE8WP6t : Office 2007 on the right ) )

    So... I do not think I had the option for Outlook or Access when I instilieret. But clearly I do. have it when I now look,,, How very Strange
    Never mind

    _.. I need to install Excel 2010 on a new computer, so I will see what happens later

    So I did a install...
    I could install Excel 2010, but it seemed to indicate that ACCESS and OUTLOOK were not on the disc...
    OutlookAndAccessNotThereWhenInstalledFromDisc.JPG : https://imgur.com/R8nftch
    This bit took a long time
    UpdatesDuringInstalaltionExcel2010fromDisk.JPG
    : https://imgur.com/qvV2F1J

    -. and in the end I have no Outlook or Access
    NoOutlookOrACCESSAfterNewInstal.JPG : https://imgur.com/A2JRe0F
    NoOutlookOrACCESSAfterNewInstalOffice2010.JPG

    Maybe it came before ( and might come later ) on an update….

    Possibly this might cause it to come later…
    IClickedForUpdatesAfterOffice2010FromDiscInstillie ren.JPG : https://imgur.com/UqTLIeB
    OfficeOpenXML FormatWTFIsLat.JPG : https://imgur.com/EVx7CKo

    So it seems that I could not install Outlook when I could instal Excel 2010... But maybe it will come later...
    ** maybe Office 2010 is or was supported for updating updates to update the Office to an updated version that does have the extra stuff


    I have not found any infomation yet to clear up the strange fact thatas
    _ I do not seem to have Outlook ( or Access) on my Office 2010 insillation disks,
    and yet
    _ appear now to have Outlook and Access installed on those computers on which I used thoes disks to install Office 2010 a few years ago.

    Yesterday I used one of those disks to install Office 2010, and as i expected I have not finally got Outlook or Access installed. I beleive this was the case originally ( but I am not 100% sure ) when I used the disks a few years ago to install the Office which now appears to have the extra Outlook and Access installed.

    Never mind, Strange goings on in the internet...




    EDIT: Later after
    I know what happened.
    The Elves came in the night and installed Outlook and Access on my computers a while back
    How nice of them
    I will add this to my list of videios to watch: https://www.youtube.com/watch?v=lPZ9_Uqu10c
    TheElvesAndTheShoemakerAndEmailUsingVBA.JPG https://imgur.com/2TFZmyR

    Thankyou little Elves. Please could you finish building my house for me so that I can sit on my computer all year instead, and not just in Winter. Thankyou in advance
    Alan and Petra ( aka Jackie )
    Last edited by DocAElstein; 02-21-2018 at 03:33 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  8. #8
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    ]VBA to automate Send and Automatically Sending of E-Mails and Excel File Workbooks

    VBA to automate Send and Automatically Sending of E-Mails and Excel File Workbooks.

    Here’s my macros … or rather macros on it.
    It seems to be potentially easy to do this and there are massive amounts of stuff on it in the internet.. at least on the easy bits… You are totally flooded with information on it if you search the internet. There is too much of it probably and a lot I looked at was very bad, so I have tried to narrow it down a bit and I reference what appeared to be the better.
    Also there is often a catch: The very simple ways require you to have already done a lot of very complicated stuff before… or you need to be very competent in adjusting many computer settings and options

    The background theory is less well documented.
    So I tried to understand a little of the background theory and made myself some notes. I share them in this post, and in the next 2 posts I get on with doing something useful, and describe everything necessary to get a fully working example
    So this post is just background info. I like to know as much as possible about the codes I use.

    Introduction. Background Theory: Computer Obfuscation
    The main background technology implemented is Computer Obfuscation:
    If you try to understand exactly what is behind all the stuff you might end up using to send an Email with VBA, then it is impossible: – you get sent around in circles of obfuscating computer terms which often, in a very indirect way ,reference each other in a very long complicated loop - The result of this is that by the time you come back where you started you forget you were there: Unknowingly you go around in circles and after a while start repeating parrot fashion bits of what you have read:- fool yourself and others into thinking you know what you are talking about
    .
    I am not criticising: I am inheritably using Polymorphism as I speak so I may be an Object Linked Oriented Hierarchical structure. I don’t know either.
    Often a lot of the stuff is wrapped up in interfaces at different levels in the computer workings. The word Interface is a term used to intimidate. It just means the bits you can get at and possibly use to do something that might be useful to someone, and what is likely dangerous.

    COM OLE Bollocks
    Somewhere deep down in software workings of a computer is some stuff, most of which Microsoft have something to do with which way back in the early 90’s tried to set some standards to allow different software to be embedded in each other. Important to the obfuscation was the use of the word “object”. The use of object has no particular meaning in this stuff, but does in others , so that helps to confuse. This is an important feature of the Obfuscation technology being used. The deep down fundamental stuff , which includes stuff been there the longest goes by the name of Component Object Model. Stuff which is often, but not always, later stuff, or at a slightly higher level of the computer workings, or slightly more to a specific application ( an actual running “runtime” usage / at an instance in time , “instance of” ) orientated goes to the name of Object Linking and Embedding

    ActiveX ActiveX Controls Security problems probably on purpose and the attempts at curing that break stuff probably on purpose as well .
    It seems most of the stuff was attempted to be put in a more user friendly set of software going under the name of Active X. This was then mainly to do with interaction of different software electronically, and inevitably that seemed to mostly end up making it possible to link stuff up over the internet. An offshoot of this is to use the programs in things that should not necessarily be directly concerned with the Internet which loosely go under the name of Active X controls. It seems to have become a major feature of this technology to allow smart computer people to get at everything everywhere. This may have been an accident or was done on purpose to allow later manipulation of everything: I don’t think anyone knows for sure. Things later going by the name often of security updates to prevent some using this ability to get at everything, tend to influence all sort of things which for an end user often means that something does not work anymore…

    API: Application Programming/Programmer Interface
    Very loosely this is the interfaces that are fairly easy to use from a fairly higher level from within a higher level programming language. In other words this allows you to get at and use some of the stuff to do with the COM OLE Bollocks from within a programming language such as VBA.
    You could even call Active X an API, but it isn’t usually for no particular reason.
    In every day casual speaking API is often referring loosely to do with using certain shipped with Windows software in Folders often having the extension dll. This extension , or rather the dll stands for direct link libraries. These are special sort of executable files of functions shared by many other ( Windows based usually ) software’s.
    Folders mostly but not always are in some way referenced using dll, either as noted with the extension or maybe referred to as dll Files or dll API files.

    dll
    It easier to understand this dll technology probably by considering what could be considered a less advanced alternative way of using such shared libraries, that being “static linked library” technology. This latter case would somehow at compile time copy all required external libraries. So for many finally complied programs a lot of duplication of things might take place , making inefficient use of computer memory in this latter case.
    The dll alternative is some sort of two step approach – a term called “exporting” somehow gives an indication of what libraries are usable to a specific application, and a term called “importeding” allows the use of the functions in a library at run time. Somehow it is all in all a more efficient way to work in a computer, the files are “executable” but exactly how no one remembers anymore. The lid is left on, it’s called an “interface” and hoped for the best.

    Using one or more dlls in a VBA code ###
    It is possible to get at a specific single or a few similar dll libraries.. To do this in VBA code an initial code line something similar to a Dim code line is done. This is a Declare type code line at the top of a code module. After that an API Function is used in a code similar to a normal VBA Function
    ( For example http://www.excelfox.com/forum/showth...tion#post10465 ).

    Libraries available to VBA
    The word Library is used sometimes like Object a bit loosely to help contribute to the Obfuscation technology used in most of the things being discussed here.
    The word Library is used often when use of dll stuff is bundled up into something like a collection of Functions and other stuff that include things that use one or more of similar dlls or use dlls that do similar stuff. This is organised something like a Visual Basic Class Object, ( An example of such a Class Object is a VBA Class Object called Worksheet ) with the available Methods Properties Constants and Functions etc which can be made available to any VBA code. The collection goes by the name of a Libraray. Specifically it is one of those Libraries that you need to put a check against in the usual way of … “……-- VB Editor -- Tools ---- references --- scroll down and check the appropriate library from the massive list of those available….”……
    Often instead of calling these things Libraries they are referred to themselves as an API. So one can see that the term is used in a very loose Obfuscation type way.

    So Finally, after all that 2 or possibly more ways to automate Send and Automatically Sending of E-Mails come out, which are similar
    One way to
    VBA to automate Send and Automatically Sending of E-Mails.
    _1 ) Way 1) Use the CDO (Collaboration Data Objects ) object library available in VBA.
    _Q) Ask a computer expert or two ( I did ) what is that CDO
    _A)
    Collaboration Data Objects (CDO), previously known as OLE Messaging or Active Messaging, is an API included with Microsoft Windows
    or
    CDO is an object library that exposes the interfaces of the Messaging Application Programming Interface (MAPI)
    _Q) What is the MAPI
    _A)
    Messaging Application Programming Interface (MAPI) is a messaging architecture and a Component Object Model based API for Microsoft Windows.
    That is probably what I would say if someone asked me. So none of us know. We just go around in circles when we try to explain and think we said something useful.

    But approximately what it means is that you are trying to get well down the level of the computer to actually do things over the internet like simple messaging and hopefully are hopefully then not too well linked to the more all encompassing and therefore dangerous Active X stuff which sort of does in a way grow out like a malignant cancer from about the same place down there deep down in the computer.
    But really no one knows.

    If I use the CDO in a VBA code then I can say that I am using the Excel API. ( I did use CDO in a VBA code, and I did just that I am using the Excel API – so there is another proof )

    If I add a few other bits along the way and disguise what I am doing a bit than I could say another application, all together: So say with me like:
    “Another Application” … This leads on to the second way:

    _2 ) Way 2)
    Office Software.. one of ‘em
    Microsoft Outlook ( an Active X Out Of Control )
    Microsoft Outlook is part of the General Microsoft Office Software Stuff. Which versions of Office it is available with is not clear yet ( http://www.excelfox.com/forum/showth...0512#post10512 )
    Mostly it seems to be available in Office, or not difficult to get. I have it in Excel 2003, don’t have it in Excel 2007, and have to rely on the ****Elves to put it on my Excel 2010.
    The mystery of the “Elves and the Shoemaker is revealed”. It was an Active X Control that went wrong and Outlook 2010 slipped through and got installed on my Office 2010

    In layman’s terms, Microsoft Outlook is a personal information manager. In this day and age it means it has an awful lot to do with the Internet, especially E-Mails, E-Mailing, Email organising etc..
    In reality it may not have been made by Microsoft.
    Microsoft Outlook was probably made using Word and Excel and the CDO by an Elf, aka an Active X OutOfControl. The same Elf or Elves installed Outlook on my Computers.

    Microsoft Outlook: what is that ( using manually )
    You would normally get the software to run on its own ( visible as it were ) in a similar way to which you might get Word or Excel to start
    For example:
    Find it single click on it:
    FindOutlook Start AllProgrammes Microsoft MicrosoftOutlook.JPG : https://imgur.com/LaGs6HA
    FindOutlook Start TypeInSearchBox Outlook.JPG : https://imgur.com/IbFOSHz
    Make a Desktop icon from a Copy/ paste and double click on it :
    MicrosoftOutlook Make a desktop Icon to double click on.JPG : https://imgur.com/ZNNPmOI

    The first time you try to open it with a click or two, a set up starts.
    Outlook2003Start.JPG https://imgur.com/tSQDoTe
    The main use of the Outlook software is “to do Email stuff”, so usually you will have at least one Email account “registered in it” You can do this at the set up or later.
    The internet is full of stuff on this, but there is no clear explanation of what it is or what it should do or how you do anything with it.
    I had a go , and here are some notes on my failed attempts:
    http://www.excelfox.com/forum/showth...0513#post10513

    After many hours I was still none the wiser, but it is worth doing all that what I did ( whatever it was ) , anyway as you may need some of that information later in one or more of the ways to send an Email using VBA.

    As far as we are concerned ( here at least ) , we can move on if we consider just this.
    Excel has Excel VBA. It can be used to automate Excel and related stuff
    Word has Word VBA. It can be used to automate Word and related stuff
    Outlook has Outlook VBA. It can be used to automate Outlook and related stuff.
    In Excel after some initial “Object Library” making within the code at the start, you can make use of other applications VBA, such as Word or Outlook, from within an Excel VBA code.
    We want to do Outlook related stuff ( Email related stuff ) in a VBA code.
    As long as we “have Outlook”, and correctly do the initial “Object Library” making within the code at the start then we can use the various Object, Methods, properties etc, which it has to offer that might be useful to us.
    ( Later we see that this is actually not quite the case as there is a big catch )

    _3 ) Way 3) Using MAPI “calls”
    Using xxAPI calls is computer slang for doing the dll codes in a VBA code via the Declare then use as a normal Function way in VBA code###
    I am guessing it should be possible to make a few MAPI Declare statements at the top of a code module and then write a VBA code to do some simple Email sending. I have not seen this done yet. I might have a go.

    _4 ) Way 4) RoutingSlip Object
    This is an object available only up to and including Office 2003. Every Workbook( up to and including Office 2003) has such an object. Primarily this is intended to allow simple sending of a Workbook.

    _5 ) Way 5) sendmail Object
    This is a method which allow simple sending of a Workbook



    Ref:
    http://www.snb-vba.eu/VBA_Excelgegevens_mailen_en.html
    https://powerspreadsheets.com/send-email-excel-vba/
    http://www.ozgrid.com/VBA/send-email.htm
    https://stackoverflow.com/questions/...books-with-vba
    Last edited by DocAElstein; 03-21-2018 at 05:33 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  9. #9
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Continued from last post…

    So I had a go at all the 5 ways discussed in the last post…
    First I spent quite a while researching all the info…

    Available stuff on the internet
    I downloaded about 40 YouTube videos, ( TheElvesAndTheShoemakerAndEmailUsingVBA.JPG : https://imgur.com/rPNV8Kd ) and found an endless amount of Blogs and other technical support and documentation. I doubt it is possible to review all the Blogs and other technical support and documentation as there is just too much.
    I narrowed down the You tube tutorials to a list approximately in order of how good I found them.

    Here is the current list:
    http://www.excelfox.com/forum/showth...0514#post10514
    I will update it from time to time


    I made a start, and share what I have done, for fun, but it is a bit biased towards what I want to do
    What I am doing.
    I am looking to do the following in a fairly direct way, and preferably in a few different Ways
    Prepare a message in Microsoft Word. Some simple formatting may be present. That formatted message will be sent per EMail with an Excel File.
    To Clarify: The Excel file is sent as an attachment. The Word File is NOT sent: I prepare my message conveniently in Word using formatting if required. I want the receiving person to see in his Email main body something similar to what I have prepared in the Word document

    The first way is probably the most “Deep down fundamental way”.
    The second way is a “bit further up”. Some of the basic stuff discussed in the first way is therefore applicable/ relevant to the second way also.

    _1 ) Way 1) Use the CDO (Collaboration Data Objects ) object library available in VBA.
    Brief Code Description:
    General overview
    This way is With the Created Microsoft CDO (Collaboration Data Objects ) object library available in VBA.
    and
    ‘With my Created LCDCW Library ( Linking Configuration Data Cods Wollups) which are used and items configured for the Exchange at Microsoft’s protocol thereof;
    http://schemas.microsoft.com/cdo/configuration/
    LCD 1.0 Library
    :
    Code:
    lcdSMTPAuthenticate lcoBasic AliAs smtpauthenticate  1
    lcdSMTPUseSSL AliAs smtpusessl
    
    lcdSMTPServer AliAs smtpserver                     "securesmtp.t-online.de"         ' Sever info         '"smtp.gmail.com" "smtp.mail.yahoo.com" "smtp.live.com" "pod51017.outlook.com"  "smtp-mail.outlook.com" "smtp.live.com"  "securesmtp.t-online.de"  465
    
    lcdSendUsingMethod lcdSendUsingPort AliAs sendusing  2
    lcdSMTPServerPort AliAs smtpserverport
    
    lcdSendUserName AliAs Doc.AElstein
    lcdSendPassword AliAs Bollox
    
    lcdSMTPConnectionTimeOut AliAs smtpconnectiontimeout AbOut 30 or thereAbOuts thereof;
     https://brettdotnet.wordpress.com/2013/06/06/send-a-gmail-email-from-excel-using-cdo/ 
    The deep down fundamental stuff , which includes stuff been there the longest goes by the name of Component Object Model. Stuff which is often, but not always, later stuff, or at a slightly higher level of the computer workings, or slightly more to a specific application ( an actual running “runtime” usage / at an instance in time , “instance of” ) orientated goes to the name of Object Linking and Embedding.
    At this lower level, there are protocols for communicating between things, and things relate are grouped into the to Office application available Library, CDO. (Collaboration Data Objects / Linking Configuration Data Cods Wollups )
    An important object there goes by the name of Message.
    As its name suggests this object is primarily concerned with electronic messaging, Emails.

    Full Code description
    Code is here: http://www.excelfox.com/forum/showth...0516#post10516
    And here : https://pastebin.com/HUuXSvsR
    Sections Walkthrough
    'Rem 1) Library made available / referencing the required object
    This is general stuff, but I am doing this a bit differently to most of the codes I found, so I explain the different ways that I know about. I am doing a form of Late Binding
    There are two main ways, explaining with my example, within Excel.
    First it should be said that regardless of which way is done , the external things such as the API codes are still used in the ddl way so are imported at run time.
    _1 Early Binding
    To use the stuff in Early Binding you have to do something extra. The “extra” thing done by early Binding I expect mainly organises the stuff in a Class Module type way. The most noticeable difference then is that as is the case when you build your own class module , you get the intellisense assistance
    The extra thing to be done is adding the reference ( In our case -- Tools --- references ---- scroll down and check Microsoft CDO for Windows 2000 )
    In layman’s terms we made Excel aware of the library of stuff
    1a) Basic Early binding ( using a variable ) “Method Set
    Dim Msg As CDO.Message
    _ Set Msg = New CDO.Message
    The first line effectively makes the variable of the type required, and the second effectively fills the variable creating an actual instance of it. Instance in this respect means that everything possibly with the thing is made available. The Dim is done at compile and so Excel VBA Knows about / is aware of it allowing intellisense. The Set effectively “makes it live.”
    1b) Auto instancing Early binding “Method Dim” ( using an auto instancing variable )
    Dim Msg As New CDO.Message
    Excel VBA knows about the type once again. But the instance has not been created. VBA is written generally in such a way that if it “knows” about a type but does not have it instance , it
    _(i) has no record of it being instanced, will always check on encountering the variable,
    and
    _(ii) if it has not been instance then it will do so. ( But nether the less on encountering it will still check every time.
    _2 Late Binding.
    This could be useful for sharing if you are not sure of what libraries someone has available. You do not make the reference check on the Library.
    You use a VBA function ( CreateObject( ) ) which at run time makes the instance of the type
    2a) Variable way.
    Dim Msg As Object
    _ Set Msg = CreateObject(“CDO.Message”)
    Because of the use of a string argument the compiler is not interested in the stuff there. The function is then written such as to search for the object of the class CDO.Message.
    2b) With End With way
    This works similarly to 2a) but removes the need for a variable. You can see how this works with my code.
    I chose the last way 2b) in my code as I had never seen it done this way in an Email with VBA code, so I am hoping there might be a good reason for that so when something goes badly wrong because of that in my code then I might learn something useful

    ‘Rem 2 ‘ protocols / conventions for interactions in Electronic Messaging
    There are broadly 2 parts to this:
    _Some deep down technical stuff
    and
    _ the data to be sent and format / conventions for passing that thereof
    '2a) ---- … This section provides the configuration information for the remote SMTP server
    The technology and coding used goes back almost 20 years and had a lot to do with early attempts at internet connections. When adding the information required some long repetitive path strings are often seen possibly just because it was never tidied up because people were scared by the Elves. There are some constants available in the Early Binding case which hide these. I wrapped then up in my own Library available through the Linking Configuration Data_Cods Wollups, LCD 1.0 Library, Interface using my object, LCD_CW = “http://schemas.microsoft.com/cdo/configuration/”
    I doubt anyone really knows WTF is going on here, but the required information sounds reasonable required infomatiuon. Some further notes on the individual parts are in the ‘Comments

    '2b) ' Data to be sent
    This section is fairly self explanatory. The conventional data from the user used in an Email is given in the required syntax.

    The only point of particular importance to note here is the addition of the Email main Body text.
    This can be specified as simple text.
    However, the computer language used mostly for the transmission of data, at least until more recent years was HTM ( https://en.wikipedia.org/wiki/HTML ). Almost all things reading transmitted electronic messages will recognise this.
    The option is available to provide the main text body in this language as a single text string.
    The tools available for producing this language are extensive.
    Amongst these tools is the possibility to save a standard Word document in a form of HTM. This can further be converted to the simple main “inner body” text part such that what you “see” in a Word document can be isolated in the HTM language form which you can then give as the Email main body text which then in most receiving Internet browsers should reproduce fairly closely the seen format in the final received Email.
    It is convenient to put that final MTM language single string in a functionalled variable, strWordHTMInnerText, which is then given in the code.
    Details to produce that are given here :
    http://www.excelfox.com/forum/showth...0517#post10517

    Rem 3 Do it
    Finally the execution takes place with the
    _ .Send
    It is not always obvious that something is happening, but an indication can be seen from the _ [Active] _ typically shown in the VB Editor window title:
    Code[Active].JPG : https://imgur.com/poib3Zp
    _.____________________________

    Note:
    If using gmail you will likely need to check / adjust some security settings. The first few You Tube Videos show this clearly.


    See YouTube Video list : https://tinyurl.com/y74cez9n

    https://www.codeguru.com/csharp/.net...-Using-CDO.htm
    https://www.rondebruin.nl/win/s1/cdo.htm
    http://www.paulsadowski.com/wsh/cdo.htm



    _2 ) Way 2)
    Office Software.. one of ‘em
    Microsoft Outlook ( an Active X Out Of Control )

    The main use of the Outlook software is “to do Email stuff”, so usually you will have at least one Email account “registered in it” You can do this at the set up or later.
    The internet is full of stuff on this, but there is no clear explanation of what it is or what it should do or how you do anything with it.
    I had a go , and here are some notes on my failed attempts:
    http://www.excelfox.com/forum/showth...0513#post10513
    After many hours I was still none the wiser, but it is worth doing all that what I did ( whatever it was ) , anyway as you may need some of that information later in one or more of the ways to send an Email using VBA.

    The internet is flooded with Blogs and code to use Outlook from within Excel VBA to send Emails.
    The codes are incredibly short and simple.
    Looking at my previous code for the CDO way , _ Sub PetrasDailyProWay1_COM_Way() _ , then the code to use Outlook from within Excel VBA to send Emails is basically the CDO way code without the part
    Code:
    '2a) 'With --------------------* my Created LCDCW Library, (LCD 1.0 Library ) (Linking Configuration Data_Cods Wollups) which are used and items configured for the Exchange at Microsoft’s protocol thereof;   http://schemas.microsoft.com/cdo/configuration/ ......This section provides the configuration information for the remote SMTP server
    This is reasonable: All this set up information is somehow already in the Outlook on your computer.. Approximately we are saying that all the “From” information , sender addresse, password etc.. is already held somewhere and the code access that..
    But: – there is the catch…
    A very important point overlooked by many, even many authors of published code is that:
    You must have a “working” Outlook set up.. I don’t. I could not figure out how to it.
    So I currently do not think it is possible to use this for a “stand alone” way to use Excel VBA to automate Email activity
    Almost half the authors of published codes that I spoke to were not actually aware of this. By co incidence they had “working” Outlooks on their computer, but has intended their codes as “standalone” VBA Excel codes just using the Outlook Libraries.., A couple decided to re read their Blog comments after I enlightened them….
    In short: If you already are very competent in Outlook and use it regularly without VBA, then doing things in it with VBA ( from Outlook, Excel, Word ) is extremely easy and just requires a few code lines and the information / Blogs on the internet are extensive. If you are not confidently using Outlook manually, then way 2 is not really a realistic option



    _._______________________

    Ways 1 and 2 Conclusions
    So that covers the first two ways.
    The first, CDO way , works very well.
    The second , Outlook way , isn’t really a way in the sense of what is being considered here.
    Most people using way 2 successfully had no idea what they were doing.
    Way 2 is like saying you are using Excel VBA to do some very simple actions on a spreadsheet, but, for some reason you have to make a big complicated Excel spreadsheet requiring all sorts of excel options set up with information that you need a lot of general Computer knowledge to do with all sorts of complicated stuff outside Excel
    Last edited by DocAElstein; 02-28-2018 at 01:51 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  10. #10
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    _3 ) Way 3) Using API “calls”
    Using xxAPI calls is computer slang for doing the dll codes in a VBA code via the Declare then use as a normal Function way in VBA code

    I almost found a way to do this.. it took a bit of unconventional thinking before I hit on an idea that almost worked..

    Execute stuff
    Some time ago computer screens were mostly black.
    The typical syntax to get something to run was just typing in something similar to the file path , or something that might be recognised by the computer as similar to, the path to the software that you wanted to run. At least that is the way I remember doing it when I was little, at least in the time in between playing with my plonker.
    Back in those days you talked about doing stuff. ( Sometimes the word Command was used for what was written or shouted as the message to do something). If you wanted to be a clever poof then you used a word like Execute instead. Computers were clever stuff back then so instead of doing programs they were “Executed”. I executed a lot of early computer stuff- I killed, trashed and broke them

    Some of the kids of about that time spent a lot of their time in Penny Arcades playing and playing on older computer. They went on to come up with things like Windows. But often the basic ideas associated with executing in terms of doing and braking stuff are still there and it goes back to when “activating a link” in electronic messaging often exposed an interface giving the possibility of getting some thing done.
    Double clicking on a file, or putting a path or URL link in a Windows explorer bar or browser bar often does something similar. The possibility of doing something as an application programmer closely related to these operations is made available in Libraries going by names similar to Windows Shell.
    Things of the “Execute” or “Command” nature often involve giving a long string reference path to expose…

    As noted previously, .. from Microsoft documentation… Visual Basic provides polymorphism through multiple ActiveX interfaces. In the Component Object Model (COM) that forms the infrastructure of the ActiveX specification, multiple interfaces allow systems of software components to evolve and break existing code. ……. In this sense interface is a set of related properties and methods. Much of the ActiveX specification is concerned with implementing standard interfaces to obtain system services or to provide malfunctionality to other programs.

    So delving deep into the innards of computing, I need to look for something, probably in a dll library that has been there a long time doing a lot of damage through the exposing of interfaces looking through long string references paths: There must be one there that the more upper level stuff has used. A good tip seems to be to look for API dll Functions that have been modified a few years back.
    http://www.jkp-ads.com/articles/apideclarations.asp
    This seems to be what “PtsSafe” is all about. It is almost impossible to find any clear documentation on what that is. But it appears to be an attempt to pull back a bit the Monster of ActiveX roaming uncontrolled in computers..

    Sure enough , a bit of searching in documentation they forgot to remove from the internet , or old books, reveals this API function.. which a typical one which need a bit of extra “If VBA7 Then Else” stuff which is done at pre compile to see if your computer is newer and so has the PtrSafe stuff

    Code:
    Option Explicit
    #If Not VBA7 Then
    Private  Declare  Function DoExecCmd Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal Operation As String, ByVal Filename As String, Optional ByVal Parameters As String, Optional ByVal Directory As String, Optional ByVal WindowStyle As Long = vbMinimizedFocus) As Long
    #Else
    Private  PtrSafe Declare  Function DoExecCmd Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal Operation As String, ByVal Filename As String, Optional ByVal Parameters As String, Optional ByVal Directory As String, Optional ByVal WindowStyle As Long = vbMinimizedFocus) As Long
    #End If
    Sub DoExecCmd()
    Dim lSuccess As Long
     Let lSuccess = DoExecCmd(0, "Open", "mailto: Doc.AElstein@t-online.de")
    End Sub
    This code brought up some Windows Email thingy.
    I guess , just like Outlook. It is there… but god knows how you use it. I tried to send something… because I had never used it before it sent me off on an attempt to set something up:
    I had a go:

    WindowsEMailSetUp 2 .JPG : https://imgur.com/S7U9cB1
    WindowsEMailSetUp 3 .JPG : https://imgur.com/7qGOznM
    WindowsEMailSetUp 4 .JPG : https://imgur.com/ww5KZAe
    WindowsEMailSetUp 5 .JPG : https://imgur.com/5nkVFB0

    It almost worked:

    I got an error
    WindowsEMailSetUpThenFail .JPG : https://imgur.com/0goEMbZ
    Code:
    Fehler bei der Verbindung zum Server. Betreff 'test', Konto: 'IMAP4', Server: 'secureimap.t-online.de', Protokoll: SMTP, Port: 25, Secure (SSL): Nein, Socketfehler: 10061, Fehlernummer: 0x800CCC0E
    
    
    Error connecting to the server. Subject 'test', Account: 'IMAP4', Server: 'secureimap.t-online.de', Protocol: SMTP, Port: 25, Secure (SSL): No, Socket Error: 10061, Error Number: 0x800CCC0E
    So I guess it is the same story as with Outlook: You need a hell of a lot of in depth computer knowledge that probably no one ever remembers anymore to set stuff up. Most people using this stuff have this set up already and probably don’t know half the time what it is they are actually doing…


    Daniel Appleman “Visual Basic Programmer's Guide to the Win32 API
    https://msdn.microsoft.com/de-de/en-.../bb979032.aspx





    _4 ) Way 4) RoutingSlip Object
    This is an object available only up to and including Office 2003. Every Workbook( up to and including Office 2003) has such an object. Primarily this is intended to allow simple sending of a Workbook.

    Any published codes which work bring up various pop ups asking for information. I have no idea what they all mean and I can’t find any documentation or Blogs to explain this or show any working example.
    I suspect this way might also require some interaction with some existing Email program.
    So I do not see it as a realistic option.


    http://www.snb-vba.eu/VBA_Excelgegev..._en.html#L_2.2
    https://msdn.microsoft.com/en-us/lib...ffice.11).aspx
    https://msdn.microsoft.com/en-us/lib...ffice.11).aspx






    _5 ) Way 5) sendmail Object
    This is a method which allow simple sending of a Workbook

    This is one single code line. The coding is not the issue: …. As with ways 2, 3 ,and 4 , it attempts to call up an existing Email program. It requires all sorts of complicated setting up specific for each computer and Email program.
    It is therefore not a practical solution in my opinion.




    https://msdn.microsoft.com/en-us/vba...l-method-excel




    Final conclusions to VBA to automate Send and Automatically Sending of E-Mails and Excel File Workbooks.

    There appears to be only one realistic way to do this: Way 1 : Use the CDO (Collaboration Data Objects ) object library available in VBA
    I was able to apply this way very well.
    It allows you to send full formatted messages and attach files completely from VBA.
    The code can be run from VBA Word or VBA Excel.
    This allows for an easy customisation to suit a particular requirement involving automating of sending E Mails with attachments.

    There do not appear to be any other ways to do this. The other published ways and codes which I investigated are very reliant on other program settings: They are dependant on existing Email programs and require extensive setting up on as particular computer system. They do not really use VBA to automate Sending Emails. They are simple additions or “add -ons” or “pseudo Buttons” to set off an existing Email program.
    I was very surprised by this… and so where almost half the authors of published codes that I contacted. I likened their sharing of their codes as similar to giving a train ticket valid from Berlin to Munich to an Aborigine wanting to go to Munich: Very useful to him… as long as someone else takes him and organises putting him on a train in Berlin.
    Ways 2, 3 ,and 4 are minor additions to something else and are no solution alone to Automatically Sending of E-Mails and attachments.











    Ref:
    http://www.snb-vba.eu/VBA_Excelgegevens_mailen_en.html
    https://powerspreadsheets.com/send-email-excel-vba/
    http://www.ozgrid.com/VBA/send-email.htm
    https://stackoverflow.com/questions/...books-with-vba
    http://www.databison.com/3-nifty-way...l-using-excel/
    Last edited by DocAElstein; 03-04-2018 at 03:57 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

Similar Threads

  1. Replies: 2
    Last Post: 02-27-2019, 05:35 PM
  2. Replies: 1
    Last Post: 03-21-2017, 05:09 PM
  3. Replies: 2
    Last Post: 08-10-2016, 04:59 PM
  4. Replies: 6
    Last Post: 06-05-2013, 11:33 PM
  5. Replies: 12
    Last Post: 05-27-2012, 08:38 PM

Tags for this Thread

Posting Permissions

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