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]
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Try this

Code:
    If MsgBox("Send Email?", vbYesNo + vbQuestion, "Email") = vbYes Then
        .send
    Else
        .display
    End If
 
Upvote 0
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]
 
Upvote 0
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
 
Upvote 0
I can not seem to get this to work. it always displays the email rather than sending it.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top