Send Email from Excel - Launch User's Default Email Client

[CorrieAnn]

I have the following code that works great on my machine. When I select a row in my spreadsheet and press the "Email Task" control, the macro will launch my personal MS Outlook app.

1) I would like it to create the email based on cursor position, instead of being dependent on the user highlighting the row.
2) It needs to create the email utilizing the user's default email client.
a) When I launch it from my desktop, it uses my personal Outlook app instead of my open, Outlook 365 web app.
b) When co-workers use the macro, it works for some and not others. It may be because the email client isn't open or that the code is not looking for Outlook 365 specifically. I really don't know. I just need it to work, regardless, which to me means it needs to look for conditions of the environment and determine what actions need to take place in order to create the email. I have no idea how to do this part.

Please don't judge my code. I know it is very rudimentary and can probably be done more efficiently, but this is what I have so far.

Sub TASKS_EmailTaskNotification()
Dim OutlookApp As Object: Set OutlookApp = CreateObject("Outlook.Application")
Dim var As Variant: var = Selection.Value
Set MyMail = OutlookApp.CreateItem(0)
          With MyMail
                    .To = var(1, 79)
                    .CC = var(1, 81)
                    .Subject = "New Task Assignment:" & " " & var(1, 1) & " -- " & var(1, 7) & " - " & var(1, 13)
                    .body = "Hello " & var(1, 82) & "," & vbNewLine & vbNewLine & _
                    "The following task is being brought to your attention for review. Please refer to the FWR Dashboard for additional details." & vbNewLine & vbNewLine & _
                    "Task ID:                                         " & var(1, 1) & vbNewLine & _
                    "Date Created:                            " & var(1, 3) & vbNewLine & _
                    "Department/Vendor:              " & var(1, 5) & vbNewLine & _
                    "Classification:                           " & var(1, 7) & vbNewLine & _
                    "Category:                                     " & var(1, 9) & vbNewLine & _
                    "Focus:                                           " & var(1, 11) & vbNewLine & vbNewLine & _
                    "Task Name:                                " & var(1, 13) & vbNewLine & _
                    "Description:                               " & var(1, 15) & vbNewLine & vbNewLine & _
                    "Details:                                         " & var(1, 17) & vbNewLine & vbNewLine & _
                    "Priority:                                         " & var(1, 19) & vbNewLine & vbNewLine & _
                    "Assigned To:                              " & var(1, 21) & vbNewLine & _
                    "Assigned By:                              " & var(1, 23) & vbNewLine & _
                    "Co-Assigned To:                       " & var(1, 25) & vbNewLine & _
                    "Assigned By:                              " & var(1, 26) & vbNewLine & _
                    "Co-Assigned Date:                  " & var(1, 28) & vbNewLine & vbNewLine & _
                    "Status:                                          " & var(1, 29) & vbNewLine & _
                    "Dependencies (Task #):        " & var(1, 31) & vbNewLine & vbNewLine & _
                    "Notes:                                           " & var(1, 33) & vbNewLine & vbNewLine & _
                    "Recommendation(s):                  " & var(1, 34) & vbNewLine & vbNewLine & _
                    "Supporting Files 1:                   " & var(1, 37) & vbNewLine & _
                    "Supporting Files 2:                   " & var(1, 38) & vbNewLine & vbNewLine & _
                    "Latest Status:                            " & var(1, 42) & vbNewLine & vbNewLine & vbNewLine & vbNewLine & _
                    "If you have any questions or concerns, please do not hesitate to reach out." & vbNewLine & vbNewLine

                End With
                MyMail.Display
End Sub

Thank you for help on this!! I have been struggling for weeks. Getting this right means our team can effectively communicate.

Office 365 (some users on web version due to traveling)
File stored on SharePoint for simultaneous, multi-user input.

 

[Micron]

1) don't know exactly what you mean by that. If the cursor is within the contents of a cell, same as if user has clicked in the formula bar, I don't think you can run code.
2) try this solution: A macro capable of creating an email in the default email client
That might resolve b) and maybe a)

If that solution isn't robust enough, perhaps this: Workbook.SendMail method (Excel)

Another option might be to use CDO for sending mail - if you can access a mail server and all users would be sending mail from that same server. This would be considerably more complicated but it would bypass the email app.

 

[CorrieAnn]

Thank you for replying.

1) As it stands right now, the user has to select the entire row before launching the macro. I would prefer that they only need to be somewhere in the row. Any cell, say, on row 10. Not in edit mode, just having that cell highlighted. My reasoning is because I wish to not display the headings on the form. If the user has to highlight the entire row in order to utilize the "Email Task" macro, I need to keep the headings visible as most users do not know keyboard shortcuts for doing this (Shft + spacebar).
2) Your CDO option most likely will not work for me, but I will look at the other two solutions you have linked to see if I can get either of those to successfully work.

Thanks again!

 

[Micron]

I get what 1) is about now. Then the cursor is irrelevant but what you'd need is to get a range for the row with the active cell. If that is a fixed range (e.g. B of the row to G of that row) that's fairly simple. Say the active cell is on row 8 when button is clicked:
range("B" & activecell.Row & ":G" & activecell.Row).Select

I only used Select as an example of one way to work with the row/range.. You rarely need to select anything to do anything to/with it.
If the range is not fixed, you'd need to find the right most column. This is one way

With ActiveSheet
     Lcol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Here's another

With ActiveSheet
    Lcol = .Cells(i, .Columns.Count).End(xlToLeft).Column

Either of those would find the right-most used column, not necessarily the right-most used column for the row with the active cell.
TBH, I'm still fumbling my way around Excel vba so I can't say when/where one method would be better than the other.