Send Email choice

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
I am using a macro to send emails. But sometimes I want to see the results first.
I can manually reset the options to do so here by changing the remark.
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]' .send '<-- Send out this email
.display'<--
Display instead for the debugging only

How can I get a prompt like this one to reset those for me?
[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]If MsgBox("Send Email?", vbYesNo + vbQuestion, "Email") = vbYes Then em = "Y"[/FONT]
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,233
Office Version
2007
Platform
Windows
Try this

Code:
    If MsgBox("Send Email?", vbYesNo + vbQuestion, "Email") = vbYes Then
        .send
    Else
        .display
    End If
 

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
Sorry I should have been clearer. This macro will process many emails. Using this reply works but i prompted at every email. I would like it to be a variable so once set it will apply to all the emails automatically. Here is the section where I have been manually changing the setting. Can this be made a variable somehow?


[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] .SentOnBehalfOfName = Behalf
'.send '<-- Directly send out this email, use
'.display '<-- Set for display only[/FONT]
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,233
Office Version
2007
Platform
Windows
Sorry I should have been clearer. This macro will process many emails. Using this reply works but i prompted at every email. I would like it to be a variable so once set it will apply to all the emails automatically. Here is the section where I have been manually changing the setting. Can this be made a variable somehow?


.SentOnBehalfOfName = Behalf
'.send '<-- Directly send out this email, use
'.display '<-- Set for display only

Try this

Code:
    'your code
    variable1 = MsgBox("Send Email?", vbYesNo + vbQuestion, "Email")
    '
    'your code
    'I suppose starts the cycle
    
        If variable1 = vbYes Then
            .send
        Else
            .display
        End If
        
    'end cycle
 

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
I can not seem to get this to work. it always displays the email rather than sending it.
 

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
I still can not get this to work. The code always displays never sends.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,233
Office Version
2007
Platform
Windows
I still can not get this to work. The code always displays never sends.
To help you with your code, I need you to put all your code.

In post #4 I gave an example of how you should adapt your code, but if you have deficultades, then, I gladly help you, but I can not continue assuming how you have the code.
 

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
I understand. There is a lot of code generating differnetn email bodies. I trimmed it to jus the emaiuling process. Hope this is sufficent. And thank you.


Code:
Sub insuremail()
' User  Permissions
    If Application.UserName = "Edward Selkov" Then ActiveSheet.UnProTect "EDS" Else GoTo xit
' Set Output
'    If MsgBox("Send Email?", vbYesNo + vbQuestion, "Email") = vbYes
 
'---------------------------------------------------------------------------------------
Email:
' Get/Create an Outlook instance
    On Error Resume Next
    Set objOutlookApp = GetObject(, "Outlook.Application")
    If Err Then
    Set objOutlookApp = CreateObject("Outlook.Application")
    IsOutlookCreated = True
    End If
    On Error GoTo 0
' Create a new email, fill it and send
    With objOutlookApp.CreateItem(0)
' Set HTML format
    .BodyFormat = 2
'Default lines
      sname = ""
      eml = "wmnce@cfs.com"
      bccl = ""
' Email Signature
    ssig = vbLf & vbLf & vbLf _
    & "Regards" _
    & sname _
    & vbLf _
    & "Contracts Team" _
    & vbLf
    ssig = Replace(ssig, vbLf, Chr(60) & "br" & Chr(62))
' Concatenate all parts for HtmlBody
    sText = shtmlheader & ssig
' Insert sText into HtmlBody
    .htmlbody = sText & "<IMG src=""C:\Users\edward.selkov\AppData\Roaming\Microsoft\Signatures\EDS_files\small.png"">"
'*******************************************************************************************************
'Specify email recipients, subject, etc:
    .To = ema
     Const Behalf = " wmnce@cfs.com " ' <-- Name to send on behalf of Exchange profile/account
    '.Cc = "carboncopy@..."
    .Bcc = bccl
    .Subject = inltr & tdd & " -- For --  " & vnd
    .SentOnBehalfOfName = Behalf
 
 
 
 '   .send '<-- Directly send out this email, use .Display instead for the debugging only
    .display
   
 
 
    End With
    GoTo res
xit:
'Prevent memory leakage
    Set objAccount = Nothing
    ema = ""
    vnd = ""
    shtmlheader = ""
 ' Quit Outlook instance if it was created by this code
    If IsOutlookCreated Then
    objOutlookApp.Quit
    Set objOutlookApp = Nothing
    End If
' Close Workbook
   ' Application.DisplayAlerts = False
   ' Workbooks("Account Representitive").Close (False)
   ' Application.DisplayAlerts = True
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,233
Office Version
2007
Platform
Windows
I understand. There is a lot of code generating differnetn email bodies. I trimmed it to jus the emaiuling process. Hope this is sufficent. And thank you.


Code:
Sub insuremail()
' User  Permissions
    If Application.UserName = "Edward Selkov" Then ActiveSheet.UnProTect "EDS" Else GoTo xit
' Set Output
'    If MsgBox("Send Email?", vbYesNo + vbQuestion, "Email") = vbYes
 
'---------------------------------------------------------------------------------------
[COLOR=#ff0000][B]Email: Where is it called?[/B][/COLOR]
' Get/Create an Outlook instance
    On Error Resume Next
    Set objOutlookApp = GetObject(, "Outlook.Application")
    If Err Then
    Set objOutlookApp = CreateObject("Outlook.Application")
    IsOutlookCreated = True
    End If
    On Error GoTo 0
' Create a new email, fill it and send
    With objOutlookApp.CreateItem(0)
' Set HTML format
    .BodyFormat = 2
'Default lines
      sname = ""
      eml = "wmnce@cfs.com"
      bccl = ""
' Email Signature
    ssig = vbLf & vbLf & vbLf _
    & "Regards" _
    & sname _
    & vbLf _
    & "Contracts Team" _
    & vbLf
    ssig = Replace(ssig, vbLf, Chr(60) & "br" & Chr(62))
' Concatenate all parts for HtmlBody
    sText = shtmlheader & ssig
' Insert sText into HtmlBody
    .htmlbody = sText & "[IMG]https://www.mrexcel.com/forum/newreply.php?do=postreply&t=1103205[/IMG]"
'*******************************************************************************************************
'Specify email recipients, subject, etc:
    .To = ema
     Const Behalf = " wmnce@cfs.com " ' <-- Name to send on behalf of Exchange profile/account
    '.Cc = "carboncopy@..."
    .Bcc = bccl
    .Subject = inltr & tdd & " -- For --  " & vnd
    .SentOnBehalfOfName = Behalf
 
 
 
 '   .send '<-- Directly send out this email, use .Display instead for the debugging only
    .display
   
 
 
    End With
    [COLOR=#ff0000][B]GoTo res  Who are you calling?[/B][/COLOR]
xit:
'Prevent memory leakage
    Set objAccount = Nothing
    ema = ""
    vnd = ""
    shtmlheader = ""
 ' Quit Outlook instance if it was created by this code
    If IsOutlookCreated Then
    objOutlookApp.Quit
    Set objOutlookApp = Nothing
    End If
' Close Workbook
   ' Application.DisplayAlerts = False
   ' Workbooks("Account Representitive").Close (False)
   ' Application.DisplayAlerts = True
End Sub
This procedure "insuremail" is called from somewhere else?

How is it that you send several emails?
Where do you have the cycle?
Or you only use GoTo, using GoTo is a bad practice.
I insist, without your complete code I can not help you.
You will have to adapt your code with what was explained in post #4
 

Watch MrExcel Video

Forum statistics

Threads
1,102,907
Messages
5,489,656
Members
407,703
Latest member
Chibuzo

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top