Link to home
Start Free TrialLog in
Avatar of Sam Coombes
Sam Coombes

asked on

Sending a email via excel using vba

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
ASKER CERTIFIED SOLUTION
Avatar of Kimputer
Kimputer

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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)
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.