Solved

Sending a email via excel using vba

Posted on 2016-10-12
6
79 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 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 15

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

786 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