Share Your Thoughts

Comments

    • Katie Stearns says

      Hi Ayush,

      Thanks so much for reading and for bringing up this question.

      As far as we know, there is no official limitation of number of emails that be sent via Excel/Outlook integration.

      • Szilviia says

        Katie – you are correct! I have not personally tested the limits so I cannot say for sure. Any limitations that do exist might be more about hardware resources versus inherent limitations in Excel or Outlook itself.

  1. Stephen Ioannides says

    Hi Katie,
    Love the simple and clear instructions – your code worked first-time – which for me is a real bonus – Question I have, if you don’t mind extending the paradigm. I want to send, in the body, both a number of specific individual data items from defined columns in the data table, and/or optionally, a block of text that comprises a cell / column range. Is it possible to add a code extension to accomplish that? Otherwise, the system, as you have coded it, works beautifully.

    • Szilvia says

      Hi Stephen.

      Your ideas sound very do-able, but a bit more information would be helpful. I will do my best here to try to imagine what you’re after and give you some things to try out…. 🙂

      As far as modifying what gets sent in the body of the email, you have a couple of options:

      1/ On the table itself, you could modify the formula used in column I such that it captures the additional information you want, ie the “specific data items from defined columns…” and / or the extra block of text. Of course this formula could get very long if you have complex requirements, so a second option….

      2/ Add some new columns in your table to capture your new requirements. For example, let’s say you used columns K and L to capture additional data / text to include in the body. In the VBA code, you could change the following line:

      theBody = Range(“I” & c.Row)

      to the following:

      theBody – Range(“I” & c.Row) & ” ” & Range(“K” & c.Row) & ” ” & Range(“L” & c.Row)

      The modified line above will concatenate columns I, K, and L, and include a space between each (the ” ” returns a space).

      I hope this gives you some ideas to start. Good luck!

      • Szilvia says

        Correction:

        theBody – Range(“I” & c.Row) & ” ” & Range(“K” & c.Row) & ” ” & Range(“L” & c.Row)

        should be an equals sign immediately following theBody, so…

        theBody = Range(“I” & c.Row) & ” ” & Range(“K” & c.Row) & ” ” & Range(“L” & c.Row)

        is the correct syntax!

  2. Vee says

    Thank you so much for this! Almost everything worked, but for some reason, when I click the VBA button “Send to Selected Contacts,” only a blank email to that person shows up without the body our subject. When I use the single-send link, the email does populate. Any chance you could point out what could be going wrong here? Thank you!!!

    • Szilvia Juhasz says

      Hi Vee!

      The only way I was able to reproduce the blank email behavior you describe was if I clicked the ‘Send to Selected Employees’ button without first selecting any employees. In other words – your cursor must be somewhere inside your table BEFORE you click the ‘Send to Selected Employees’ button (otherwise the macro doesn’t “know” which employee you are concerned with, so it creates a blank email). The correct way to do this is described in detail in Step 3 (“Highlight to select one or more Employee Names in your Table”) in the “Send Your Emails!” section of this article.

      IF you’ve confirmed you are following this step correctly and it’s still not working, some other things to confirm:

      1/ Make sure there is something in the ‘Email Body’ column, and that this is column I

      2/ Make sure there is something in the ‘Email Subject’ column, and that this is column H

      I hope this helps… Keep us posted!

      • Szilvia Juhasz says

        EDIT: Regarding employee selections. You can actually position your cursor OUTSIDE the table, as long as it is positioned in one of rows occupied by the table data. In the example for this article, that would mean rows 2:6!

  3. Mathew says

    Thank you very much Szilvia for this great tutorial!

    Do you know if there is a way to send out these emails on a daily basis at a given time without hitting the send button everyday?

    • Szilvia Juhasz says

      Hi Matthew. The short answer is YES, it’s possible! Scheduled tasks can be accomplished with Power Automate (too much explainery for one comment), or VBA. Both have pros & cons, similar to the ones I describe in this article.

      If you’re looking to use VBA macros to do this, I would suggest exploring something called the “OnTime” VBA method. For example, the following line of code would run a macro called “MyMacro” (stored in Module 1 of a workbook named ‘Emailer.xlsx”) at 4:00PM every day:

      Application.OnTime TimeValue(“4:00 PM”), “C:\MyStuff\Emailer.xlsx!Module1.MyMacro”

      Some gotchas: Application.OnTime can be tricky. If you happen to be doing something else in Excel at exactly 4PM, you might get an error because Excel has too much going on. There are other approaches you could consider, but I think it would be simplest to just click the button! It’s only one click after all, how long does THAT take, really??? 😉

      Hope this is helpful to get started.

    • Szilvia Juhasz says

      Hi Ty! Yes, this is possible to do, it would require two steps: 1/ Insert a new column in your table to input your cc email addresses. 2/ Modify the VBA code to recognize the new column and the fact that you want to include a cc recipient.

      Here is a detailed example of how to do this:

      Let’s say you have inserted a new column next to ‘Send To’. You will use this column (D) to enter the address(es) of the cc recipients. (Note, if you have multiple recipients in your new ‘CC’ column (D), you can add them all, using semicolon to separate them, the same way you would if you were typing directly in the Outlook email. You can do the same for the ‘Send To’ column, if you have more than one main recipient you want to include)

      Now that you have this new column D, note that all subsequent columns have shifted over by one column to the right. Because of this, and because we have new CC recipients, we need to modify the VBA code in two sections:

      1/ In this part….note I added the new row for a new variable called “CCName”:

      For Each c In Selection ‘loop through (manually) selected records
      ”’For each row in selection, collect the key parts of
      ”’the email message from the Table

      SendToName = Range(“C” & c.Row)
      CCName = Range(“D” & c.Row)
      theSubject = Range(“I” & c.Row)
      theBody = Range(“J” & c.Row)

      2/ In this part, note I have once again added a new row to include the cc recipient in my email:

      ”’Compose the customized message
      With oMail
      .To = SendToName
      .cc = CCName
      .Subject = theSubject
      .Body = theBody

      That’s about it! This will allow you to now send to cc recipients.

      Good luck!

    • Szilvia Juhasz says

      Hi Alina,
      Do you need to send the same attachment to every email? If it’s just a few emails you want to add the attachment to, the easiest approach would be to simply attach the file once you have the draft emails created. Add the attachments the way you normally would in any email. This is the easiest way – but not the fastest. Big difference, take your pick!

      However if you are up for the challenge, and you want to do this in a more automated way, the approach is very similar to what I described in an earlier question from TY in this blog post! In your case, you could create a new column in your table to store the source file information. But instead of CCName, you can name your column “Attachment”. In this new column you would then add the full path / filename of the desired attachment.

      For example, let’s say this new column is now D. The modified code sections would look something like this:

      SendToName = Range(“C” & c.Row)
      AttachmentsInfo = Range(“D” & c.Row) ‘NEW line of code
      theSubject = Range(“I” & c.Row)
      theBody = Range(“J” & c.Row)

      With oMail
      .To = SendToName
      .Subject = theSubject
      .Body = theBody
      .Attachments.Add AttachmentsInfo ‘NEW line of code

      I also found this article which explains in more detail, which you can try to adapt to your file:
      https://wellsr.com/vba/2018/excel/excel-vba-send-email-with-attachment/

      Good luck!

  4. ILIR DANAJ says

    Hi Szilvia Juhasz, really amazing job.
    I want to send an automated email, not for every cell that I change, because normally I will change a lot of cells, and I do not want to send a lot of email, but just one. So to send an automated email, after making changes in the workbook, after 1 hour. Is it possible? TIA.

    • Szilvia Juhasz says

      Hi ILIR DANAJ

      I am hearing two questions here, so I’ll address them separately.

      1/ “I don’t want to send a lot of email, but just one”
      Please revisit Step 3 in my original post, and note the part about single-send options when you only have one email to send.

      2/ “… after 1 hour”
      I think you are asking whether it’s possible to do a scheduled send? If so, see my answer to Matthew’s question above (Feb 8) . In my experience using VBA to do scheduled sends based on time isn’t really the best option. There are better / more modern methods that Microsoft has introduced like Power Automate.. but that’s a topic for another whole post and beyond the scope of this one!

      Good luck!

  5. ilir danaj says

    Hi Svilvia, thank you for answering to my question.
    This is the scrip that I’m trying to apply. So for any change that I will make in the file, I want to send and automated email only once after 1 hour, only if there are changes.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRgSel As Range
    Dim xOutApp As Object
    Dim xMailItem As Object
    Dim xMailBody As String
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.OnTime TimeValue(“20:00:00”), “SendEmail”
    ‘Application.OnTime Now + TimeValue(“00:01:00”)
    Set xRg = Range(“A2:NF33”)
    Set xRgSel = Intersect(Target, xRg)
    ActiveWorkbook.Save
    If Not xRgSel Is Nothing Then
    Set xOutApp = CreateObject(“Outlook.Application”)
    Set xMailItem = xOutApp.CreateItem(0)
    xMailBody = “Hello people” & “.” & vbNewLine & vbNewLine & _
    “Monthly schedule has been updated” & “.” & ” ” & vbNewLine & _
    “Cell(s) ” & xRgSel.Address(False, False) & _
    ” in the worksheet ‘” & Me.Name & “‘ were modified on ” & _
    Format$(Now, “mm/dd/yyyy”) & ” at ” & Format$(Now, “hh:mm:ss”) & _
    ” by ” & Environ$(“username”) & “.” & ” ” & vbNewLine & _
    “Please check your schedule for any changes” & “.”
    With xMailItem
    .To = “mail1@myemail.al”
    .CC = “mail@myemail.al”
    .Subject = “Worksheet modified in ” & ThisWorkbook.FullName
    .Body = xMailBody
    .Send
    End With
    Set xRgSel = Nothing
    Set xOutApp = Nothing
    Set xMailItem = Nothing
    End If
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub

    • Szilvia Juhasz says

      Hello..Based on what I think you’re asking I’m not sure what I can advise in a quick post here. I am curious to know whether you have tested this code out? If so, did it work in it’s current form?

      Regarding your remark: “only if there are changes in the last hour..”: Did you already devise a mechanism to actually detect whether changes have occurred? I’m guessing not, because this is precisely where it gets complicated: Change tracking on Excel desktop files is not easy. In Excel online it’s much easier – but the ‘gotcha’ here is, macros cannot run in the cloud. So you’d have two platforms to juggle: Excel desktop and Excel online. Yet another blog post if you really wanted to go that route.

      Before you go there: I really think Power Automate is a better alternative if you want BOTH things simultaneously: 1/ an efficient way to track when changes have been made, AND 2/ the ability to automatically send emails from Excel based on whether things HAVE changed. One big advantage with Power Automate is that it is more aligned with the “future” of Excel. It is a fact that VBA will become less and less important for many tasks in the near / long term future, even though VBA is still used in A LOT of spreadsheets right now, including certain models I create for myself and my clients!

      I suggest check out the following tutorial by Mynda Treacy on sending emails from Excel with Power Automate : https://youtu.be/1g7NA5hYYSo to see if this might be an option for you!

      Good luck

  6. Daniele Martinez says

    Hi,
    Thank you for this explanation!
    Everything works great, except that once the email is created, my signature is not included (I need to manually add it).
    Is it possible to add it to the macro?
    Or am I doing something wrong?

    Thank you
    Daniele

    • Szilvia Juhasz says

      Hi Daniele
      There are several ways to add a signature but the code gets a bit more complicated! I’m looking into whether there is an “easy” way… first question is: do you have a picture in your default signature? Let me know. Thanks.

      • Jon Beamer says

        I am attempting to do the same thing and there is an image in our organization signature. I was wondering, if workbook is on a shared drive, could multiple employees use it at separate times to generate an email with their own default signature? – The only real difference in signature would be status and name.

        I was also wondering if it were possible to create a PDF out of one of the spreadsheets on the workbook and attach that to the email. If you require any more information, please let me know.

  7. Bhumi says

    Hi Szilvia,

    Thank you for this super helpful and easy to understand tutorial. I have a question – The body of my email has slightly longer and has a touchbase table template where I am trying to get responses to specific questions from my employees. When I try to copy past that templte, excel gives me an error saying “Text values in formula are limited to 255 characters. To create longer text values in the formula, use the CONCENATE function or the concatenation operator(&).” How can I resolve this. Is there a way I can send you the email template I am trying to paste? It won’t let me attach a screenshot here.

    Any help will be super appreciated.

    Thank you!!

    • Szilvia Juhasz says

      Hi Bhumi
      Yes there is a built in limitation in Excel for character count in any cell. Unfortunately there isn’t an easy workaround on the Excel side in the example we’ve illustrated here. A couple of approaches to explore:
      1. Consider using a more feature rich email program like MailChimp!
      2. Create two (or three, depending on how long the replies are) separate columns for ‘Email Body’ and do the “concatenation” on the VBA code side instead. I’m not sure this makes your life easier, however, as you would still be faced with the challenge of copying part of the employee response into one column, and the other part into subsequent columns… Then then code would look something like this:

      ub EmailAllTEST()
      Dim oApp As Object
      Dim oMail As Object
      Dim SendToName As String
      Dim theSubject As String
      Dim theBody As String

      For Each c In Selection ‘loop through (manually) selected records
      ”’For each row in selection, collect the key parts of
      ”’the email message from the Table

      SendToName = Range(“C” & c.Row)
      CCName = Range(“D” & c.Row)
      theSubject = Range(“I” & c.Row)
      theBodyParagraphOne = Range(“J” & c.Row) ‘reads columns J and K for the two paragraphs
      theBodyParagraphTwo = Range(“K” & c.Row)

      ”’Compose emails for each selected record
      ”’Set object variables.
      Set oApp = CreateObject(“Outlook.Application”)
      Set oMail = oApp.CreateItem(0)

      Signature = oMail.body

      ”’Compose the customized message
      With oMail
      .To = SendToName
      .cc = CCName
      .Subject = theSubject
      .body = theBodyParagraphOne & vbNewLine & theBodyParagraphTwo ”splits the email text into two paragraphs

      ”’ rest of the code stays the same as original…

      I think using an email marketing program is going to be easier. Alternatively, see my previous answers regarding Power Automate. While this is arguably a “no code required” type approach, there is still a learning curve there as well.

      I don’t know if that was helpful but I hope you can appreciate my candor ¯\_(ツ)_/¯

  8. MICHAEL LEVETT says

    Hi Bhumi,

    Fantastic tutorial, thank you so much. I was wondering if it is possible to put a formula in place to consolidate data from multiple rows into one email if the ‘send to’ email address is the same?

    Any help would be much appreciated.

    Regards
    Mike

  9. Matthew says

    Hi, very well explained, thanks for this information. Was just wondering whether you could help with explaining how to change the account from which the emails are automatically sent as I have more than one mailbox. Thanks a lot 🙂

  10. Neville Parker says

    Hi there,

    This is a great tutorial. I’ve managed to get it all functioning using the send hyperlinks on a modified spreadsheet. I can’t get the macro to run. The debugger stops here:

    Set oApp = CreateObject(“Outlook.Application”)

    I have another program set as the default email app on my iMac. I also use outlook version 2016 for Mac.

    Is there a setting I need to change?

  11. Neville says

    Hi there,

    Thanks for this fantastic walkthrough. I’ve managed to get everything working up to the send single link point for a student results spreadsheet. I’m having problems with the macro though and hope that you might be able to help. Running the debugger highlights this line:

    Set oApp = CreateObject(“Outlook.Application”)

    I’m running excel Mac 2016 with a 365 log in.

    Hope you can help, thanks

    Nev

Leave a Reply

Your email address will not be published.

Know where your time goes
BeeBole Timesheet is helping more than 1000 companies in the world to optimize time and resources.
  1. Flexible time-tracking
  2. Business reports
  3. 1-1 support
  4. API and integrations
  5. Multilingual
  6. Secure
Send this to a friend