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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,117
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,117
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,117
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,117
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,050
Messages
5,484,417
Members
407,438
Latest member
DKrakken

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top