Solved

Sending a email via excel using vba

Posted on 2016-10-12
6
114 Views
Last Modified: 2016-11-10
Does anyone know how to  amend this code inorder that I can send it from a different outlook account. I already know the account item number which is 2 I just cant work out how to get this to work in my code. Really hoping someone can help me.

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Sub SendEMail()
    Dim Email As String, Subj As String
    Dim Msg As String, URL As String
   
   
   
    Email = Cells(ActiveCell.Row, 19)
       
    Subj = Cells(ActiveCell.Row, 4)

    Msg = ""
    Msg = Msg & "Dear " & Cells(ActiveCell.Row, 1) & "," & vbCrLf & vbCrLf & "Here is some precanned text before the BODY info in the spreadsheet. " & vbCrLf & vbCrLf & Cells(ActiveCell.Row, 13) & vbCrLf & vbCrLf & " And here is some more precanned text in the macro AFTER the Body stuff."
   
    'Replace spaces with %20 (hex)
    Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
    Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")
   
    'Replace carriage returns with %0D%0A (hex)
    Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
   
    'Create the URL
    URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg
   
   
   
   
    'Execute the URL (start the email client)
    ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus
   
    'Wait two seconds before sending keystrokes
    'Application.Wait (Now + TimeValue("0:00:02"))
    'Application.SendKeys "%s"
End Sub
0
Comment
Question by:Sam Coombes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
6 Comments
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
ID: 41841523
I would suggest, to either:

if logging to sent items is NOT important, change code to SMTP only.

If logging to sent items is important, and it's saved server side (Exchange/Office365), change it to have Outlook to have multiple profiles. Adjust VBA code to open Outlook with the other profile, and send using the MailItem, not the shellexecute

or
(least code changes needed)
Have Outlook have 2 profiles, with startup prompt. Then adjust only the last part of your current code to send keys for cursor down (starting the other profile).

Your current method (firing shellexec on the mailto: url) can't programatically adjust any settings regarding accounts etc.
1
 
LVL 12

Expert Comment

by:tel2
ID: 41876413
Hi frankhelk,

I see you are proposing this question be deleted for the reason:
   "Not enough information to confirm an answer".

I think Kim has quite possibly provided enough info to answer the question, or at least point Sam in the right direction so he can solve the details himself.

Sam's come back with no follow-up questions.

In my view, deleting this question now would open the EE process to abuse, and means that experts can give their valuable time and knowledge for no credit (points), and with no feedback from the asker, the expert has no (efficient) opportunity to fill in any gaps in the asker's mind.  Experts should not have to be mind readers, or spend their time preempting every possible scenario with no direction from the asker.  If all an asker has to do to circumvent the EE question/answer/credit process is nothing, then I think the process has a significant flaw.

And Kim's post may well help future EE users, who may find this question, to find solutions also, too.  It's hard to judge.

The only person who has given "Not enough information to confirm an answer" is the questioner (i.e. Sam).  But judging by the fact that Sam has clicked the "Endorse" button under Kim's answer, it sounds as if he likes his answer.

Thanks for your consideration of this objection to deleting this question.

tel2
0
 
LVL 12

Expert Comment

by:tel2
ID: 41881533
True Kyle, but I hope that such an endorsement would not be required to award points in the absence of feedback from the asker.  That was just an additional point in the favour of the expert.  If EE wants to be fair, then I suggest they consider giving experts the benefit of the doubt where no other info is supplied.  Doing so doesn't cost the asker anything extra, but rewards the expert(s) for their efforts.

Not that I'm in any official position to make any rulings here.  Just to make suggestions from what seems fair, reasonable and common sense from my perspective.

tel2
Vigilante Fairness Police              8)
0
 
LVL 16

Expert Comment

by:Kyle Santos
ID: 41882369
If EE wants to be fair, then I suggest they consider giving experts the benefit of the doubt where no other info is supplied.
Yes, technically, it does consider giving the Experts the ability to close questions. :)

http://support.experts-exchange.com/customer/portal/articles/2574828

Thanks for being vocal about this.  Message me if you have any questions.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question