Solved

Sending a email via excel using vba

Posted on 2016-10-12
6
56 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
  • 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 11

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 11

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 13

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now